group by comparision - 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)

group by comparision

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-03-25 : 19:20:44

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-25 : 20:48:31

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

mike13
Posting Yak Master

219 Posts

Posted - 2013-03-26 : 04:29:39

Wow thanks a lot.

That did the trick!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources