simple Group by question - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

simple Group by question

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-01 : 19:18:02

Hi all,

I'm trying this group by to try to find out how many of our customers in the DB actually have shipped orders.

i tried this:

SELECT COUNT(*) AS amount, T_Customer.country
FROM T_Customer INNER JOIN
T_Order_Main ON T_Customer.CustomerID = T_Order_Main.CustomerID
WHERE (T_Order_Main.Orderstatus = 8)
GROUP BY T_Customer.country, T_Order_Main.Orderstatus
ORDER BY amount DESC

But it is giving back to many records, probably counting each order instead of just the customers that have shipped orders.
T_Order_Main.Orderstatus = 8
means shipped order

thanks a lot

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 19:29:10

Change the "COUNT(*)" to "COUNT(Distinct T_Customer.CustomerId)"

SELECT COUNT(DISTINCT T_Customer.CustomerID) AS amount,
       T_Customer.country
....

Now, that won't be amount - it would be the number of customers. So the alias doesn't seem quite right.

mike13
Posting Yak Master

219 Posts

Posted - 2012-11-02 : 04:07:44

thanks a lot that did the trick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources