HAVING/GROUP BY , MULTIPLE AGGREGATES

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

General SQL Server Forums

New to SQL Server Programming

HAVING/GROUP BY , MULTIPLE AGGREGATES

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-18 : 09:37:16

I have this problem but and my code doesn't return any rows.

List resellers and total sales for each for 2008.
-- Show Reseller name, business type, and total sales.
-- List only those resellers having sales exceeding $150,000.
-- 10 Rows

SELECT R.ResellerName, R.BusinessType, SUM(ROUND(S.[OrderQuantity] * S.UnitPrice, 2)) AS TotalSales
FROM dbo.DimReseller AS R
INNER JOIN dbo.FactResellerSales AS S
ON R.ResellerKey = S.ResellerKey
GROUP BY R.ResellerName, R.BusinessType, S.OrderDate
HAVING SUM(S.OrderQuantity * S.UnitPrice) > 150000 AND Year(S.OrderDate) = '2008'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-18 : 09:41:24

try

GROUP BY R.ResellerName, R.BusinessType, S.OrderDate

also
year() will return an integer, you does not need to specify the year 2008 in string

Year(S.OrderDate) = 2008

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

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-18 : 09:48:48

if I omit S.OrderDate from GROUP BY, it will return this error:

Column 'dbo.FactResellerSales.OrderDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

I set to YEAR(S.OrderDate) = 2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-18 : 10:22:38

only place aggregate condition in the HAVING.
none-aggreggate condition place in WHERE


SELECT     R.ResellerName, R.BusinessType, SUM(ROUND(S.[OrderQuantity] * S.UnitPrice, 2)) AS TotalSales
FROM       dbo.DimReseller AS R
INNER JOIN dbo.FactResellerSales AS S
        ON R.ResellerKey = S.ResellerKey
WHERE      Year(S.OrderDate) = 2008
GROUP BY   R.ResellerName, R.BusinessType
HAVING     SUM(S.OrderQuantity * S.UnitPrice) > 150000

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

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-18 : 16:44:31

thanks khtan, when I ran it, I get 11 rows. I am supposed to get 10 rows. Maybe the answer is wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-18 : 21:42:29

quote:
Originally posted by rv498

thanks khtan, when I ran it, I get 11 rows. I am supposed to get 10 rows. Maybe the answer is wrong?

I am not sure, but verify the result of the query against the data in the table

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

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-18 : 21:46:51

I got it. It's this. returns correct # of rows.

SELECT ResellerName, BusinessType, SUM([OrderQuantity] * ProductStandardCost) AS TotalSales
FROM dbo.DimReseller AS R
INNER JOIN dbo.FactResellerSales AS S
ON R.ResellerKey = S.ResellerKey
WHERE YEAR(OrderDate) = 2008
GROUP BY ResellerName, BusinessType
HAVING SUM(OrderQuantity * ProductStandardCost) > 150000
ORDER BY ResellerName, BusinessType

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-18 : 22:30:34