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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources