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)
 count with groups

Author  Topic 

mike13
Posting Yak Master

219 Posts

Posted - 2013-10-08 : 12:31:31
Hi All,

I got this statement:

SELECT TOP (100) PERCENT dbo.T_Gateways.p_name, YEAR(dbo.T_LOG_Order_Gateway.orderdate) AS Y, MONTH(dbo.T_LOG_Order_Gateway.orderdate) AS M, DAY(dbo.T_LOG_Order_Gateway.orderdate) AS D,
SUM(dbo.T_LOG_Order_Gateway.amount) AS SumAmount
FROM dbo.T_LOG_Order_Gateway INNER JOIN
dbo.T_Gateways ON dbo.T_LOG_Order_Gateway.gatewayid = dbo.T_Gateways.id
GROUP BY YEAR(dbo.T_LOG_Order_Gateway.orderdate), MONTH(dbo.T_LOG_Order_Gateway.orderdate), DAY(dbo.T_LOG_Order_Gateway.orderdate), dbo.T_Gateways.p_name
ORDER BY Y, M, D

But i want to know how many orders where in that sum?
how can i do that?

Thanks,

Mike

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-08 : 12:57:08
Add a count column to the query like shown below? If that is not what you are looking for, try the two other possibilities below that, that I have commented out:
SELECT TOP ( 100 ) PERCENT
dbo.T_Gateways.p_name ,
YEAR(dbo.T_LOG_Order_Gateway.orderdate) AS Y ,
MONTH(dbo.T_LOG_Order_Gateway.orderdate) AS M ,
DAY(dbo.T_LOG_Order_Gateway.orderdate) AS D ,
SUM(dbo.T_LOG_Order_Gateway.amount) AS SumAmount
,COUNT(*) AS NumOrders
--,COUNT(DISTINCT T_LOG_Order_Gateway.Order_id) AS NumOrders
--,COUNT(*) OVER () AS NumOrders

FROM dbo.T_LOG_Order_Gateway
INNER JOIN dbo.T_Gateways ON dbo.T_LOG_Order_Gateway.gatewayid = dbo.T_Gateways.id
GROUP BY YEAR(dbo.T_LOG_Order_Gateway.orderdate) ,
MONTH(dbo.T_LOG_Order_Gateway.orderdate) ,
DAY(dbo.T_LOG_Order_Gateway.orderdate) ,
dbo.T_Gateways.p_name
ORDER BY Y ,
M ,
D
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 13:26:26
you may be better off showing how order is present in table.Is it one record per OrderID thats present in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -