Aid with Aggregate Values and Derived Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Aid with Aggregate Values and Derived Tables

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-29 : 11:39:37

Hello,

How can I write this code so that I can add the WHERE clause (in bold) ?


USE myMarket
GO

  SELECT STDEVP(c.PublOffers) As StandardDev 

  FROM
   (
    SELECT theDate, SUM(PublishedOffers) As PublOffers
    FROM PublOffersControl 
    WHERE MONTH(Data) = 3 And YEAR(Data) = 2006 And Antiquity = 7 
    GROUP BY theDate

   ) As c 

  WHERE User_num IN(277, 288) --User_num is a column of 'PublOffersControl' table.

I can' t 'GROUP BY' User_number because to get the statistical number STDEVP (Standard Deviation) correctly I need to 'GROUP BY' theDate column only in the selection/derived table (c)

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-29 : 11:41:53

[code] SELECT STDEVP(c.PublOffers) As StandardDev
FROM
(
SELECT theDate, SUM(PublishedOffers) As PublOffers
FROM PublOffersControl
WHERE MONTH(Data) = 3 And YEAR(Data) = 2006 And Antiquity = 7
AND User_num IN (277, 288)
GROUP BY theDate[/code]
) As c

KH
[spoiler]Time is always against us[/spoiler]

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-29 : 11:52:49

I' m so sorry... I was totally blind!

Thank you !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources