SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HAVING/GROUP BY , MULTIPLE AGGREGATES
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/18/2014 :  09:37:16  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 03/18/2014 :  09:41:24  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 03/18/2014 09:42:39
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/18/2014 :  09:48:48  Show Profile  Reply with Quote
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

Edited by - rv498 on 03/18/2014 09:49:38
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 03/18/2014 :  10:22:38  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 03/18/2014 10:24:24
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/18/2014 :  16:44:31  Show Profile  Reply with Quote
thanks khtan, when I ran it, I get 11 rows. I am supposed to get 10 rows. Maybe the answer is wrong?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 03/18/2014 :  21:42:29  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/18/2014 :  21:46:51  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 03/18/2014 :  22:30:34  Show Profile  Reply with Quote
quote:
-- List only those resellers having sales exceeding $150,000.

you are using COST in your calculation but the question stated SALES ?

maybe the question is wrong ?


KH
Time is always against us

Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/18/2014 :  22:40:57  Show Profile  Reply with Quote
Yes, maybe :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000