Help with group by - 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 2005 Forums

Transact-SQL (2005)

Help with group by

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2008-02-06 : 13:31:15

Hi,

I have this statement, that gives me the total of month for all countries i sold to.

SELECT TOP (100) PERCENT SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals, dbo.T_Customer.country
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (dbo.T_Order_Main.Shipdate >= '10-01-2005') AND (dbo.T_Order_Main.Shipdate <= '11-1-2005')
GROUP BY dbo.T_Customer.country
ORDER BY totals

What i want is that i can see the every month
so that the result insted of being:

USA - 12000
Canada - 1000

it would be

JAnuary - USA - 12000
January - Canada - 1000
February - USA - 12000
February - Canada - 1000

or the month in numbers is also okay.

Thanks a lot,

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:38:15

SELECT TOP (100) PERCENT SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals, dbo.T_Customer.country,MONTHNAME(dbo.T_Order_Main.Shipdate)
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (dbo.T_Order_Main.Shipdate >= '10-01-2005') AND (dbo.T_Order_Main.Shipdate <= '11-1-2005')
GROUP BY MONTHNAME(dbo.T_Order_Main.Shipdate),dbo.T_Customer.country
ORDER BY totals

mike13
Posting Yak Master

219 Posts

Posted - 2008-02-06 : 14:11:40

Perfect

is it possible to add the year also?
ex:
2007 -JAnuary - USA - 12000
2007 -January - Canada - 1000
2007 -February - USA - 12000
2007 -February - Canada - 1000

tnx a lot,

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 22:41:44

quote:
Originally posted by mike13

Perfect

is it possible to add the year also?
ex:
2007 -JAnuary - USA - 12000
2007 -January - Canada - 1000
2007 -February - USA - 12000
2007 -February - Canada - 1000

tnx a lot,

Mike

SELECT TOP (100) PERCENT SUM(dbo.T_Order_Detail.Quantity * dbo.T_Order_Detail.Cost) AS totals, dbo.T_Customer.country,MONTHNAME(dbo.T_Order_Main.Shipdate),YEAR(dbo.T_Order_Main.Shipdate)
FROM dbo.T_Order_Main INNER JOIN
dbo.T_Customer ON dbo.T_Order_Main.CustomerID = dbo.T_Customer.CustomerID RIGHT OUTER JOIN
dbo.T_Order_Detail ON dbo.T_Order_Main.ORDERID = dbo.T_Order_Detail.OrderID
WHERE (dbo.T_Order_Main.Orderstatus = 8) AND (dbo.T_Order_Main.Shipdate >= '10-01-2005') AND (dbo.T_Order_Main.Shipdate <= '11-1-2005')
GROUP BY YEAR(dbo.T_Order_Main.Shipdate),MONTHNAME(dbo.T_Order_Main.Shipdate),dbo.T_Customer.country
ORDER BY totals

mike13
Posting Yak Master

219 Posts

Posted - 2008-02-07 : 03:11:27

Cool, thanks a lot!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources