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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 group by comparision
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 03/25/2013 :  19:20:44  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi all

i got this statement that give me the total orders shipped by country for each month in year 2012.

SELECT COUNT(*) AS totals, CAST(MONTH(T_Order_Main.Shipdate) AS varchar(100)) AS M, T_Customer.country
FROM T_Order_Main INNER JOIN
T_Customer ON T_Order_Main.CustomerID = T_Customer.CustomerID
WHERE (T_Order_Main.Orderstatus = 8) AND (YEAR(T_Order_Main.Shipdate) = 2012)
GROUP BY MONTH(T_Order_Main.Shipdate), T_Customer.country
ORDER BY M, totals


I like to make a statement that show ex:
country month 1, month 2 ....
Germany 10, 20

thanks a lot

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 03/25/2013 :  20:48:31  Show Profile  Reply with Quote
You can use the PIVOT operator. If you are going to have data for more than one year, you should also add a year column.
SELECT * FROM
(
SELECT 
	COUNT(*) AS totals,
	CAST(MONTH(T_Order_Main.Shipdate) AS varchar(100)) AS M, 
	T_Customer.country
FROM 
	T_Order_Main 
	INNER JOIN T_Customer ON T_Order_Main.CustomerID = T_Customer.CustomerID
WHERE 
	(T_Order_Main.Orderstatus = 8) 
	AND T_Order_Main.Shipdate >= '20120101' and T_Order_Main.Shipdate < '20130101'
GROUP BY 
	MONTH(T_Order_Main.Shipdate),
	T_Customer.country
)s
PIVOT (MAX(Totals) for M in ([1],[2],[3],[4],[5],[6],[7],[8 ],[9],[10],[11],[12])) P

ORDER BY country
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 03/26/2013 :  04:29:39  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Wow thanks a lot.

That did the trick!
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.03 seconds. Powered By: Snitz Forums 2000