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
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-03-26 : 04:29:39
Wow thanks a lot.

That did the trick!
Go to Top of Page
   

- Advertisement -