• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

SQL Statement for Grouping orders from 50 States

I need to be able to grid out sales from 50 states by month.

The database I have is SQL and the table is called: Orders
In the Table there are the following fields:

OrderID, OrderTotal, OrderState, OrderCompletedDate (the rest, I imagine is irrelevant)

I need to create a grid of 50 states (Vertical) and 12 months (current year- Horizontal) and insert figuers of order totals for each.

I haven't the foggiest idea of how to get this done and I am under a lot of presure to magically come up with a solution. Is there a genie on this website?


0
EGormly
Asked:
EGormly
  • 2
  • 2
  • 2
1 Solution
 
RPCITCommented:
I know that MSSQL 2005 has some pivot fuctionality...  but depending on your SQL server.. that may not be an option.. (and I'm not exactly familiar with how to do it...

You could do something like this though...


SELECT OrderState,	OrderYear,
		SUM(January) AS January, SUM(February) AS February, SUM(March) AS March,
		SUM(April) AS April, SUM(May) AS May, SUM(June) AS June,
		SUM(July) AS July, SUM(August) AS August, SUM(September) AS September,
		SUM(October) AS October, SUM(November) AS November, SUM(December) AS December
FROM (
	SELECT OrderState, YEAR(OrderCompletedDate) AS OrderYear,
		 CASE WHEN MONTH(OrderCompletedDate) = 1 THEN OrderTotal ELSE 0 END AS January,
		 CASE WHEN MONTH(OrderCompletedDate) = 2 THEN OrderTotal ELSE 0 END AS February,
		 CASE WHEN MONTH(OrderCompletedDate) = 3 THEN OrderTotal ELSE 0 END AS March,
		 CASE WHEN MONTH(OrderCompletedDate) = 4 THEN OrderTotal ELSE 0 END AS April,
		 CASE WHEN MONTH(OrderCompletedDate) = 5 THEN OrderTotal ELSE 0 END AS May,
		 CASE WHEN MONTH(OrderCompletedDate) = 6 THEN OrderTotal ELSE 0 END AS June,
		 CASE WHEN MONTH(OrderCompletedDate) = 7 THEN OrderTotal ELSE 0 END AS July,
		 CASE WHEN MONTH(OrderCompletedDate) = 8 THEN OrderTotal ELSE 0 END AS August,
		 CASE WHEN MONTH(OrderCompletedDate) = 9 THEN OrderTotal ELSE 0 END AS September,
		 CASE WHEN MONTH(OrderCompletedDate) = 10 THEN OrderTotal ELSE 0 END AS October,
		 CASE WHEN MONTH(OrderCompletedDate) = 11 THEN OrderTotal ELSE 0 END AS November,
		 CASE WHEN MONTH(OrderCompletedDate) = 12 THEN OrderTotal ELSE 0 END AS December
	FROM Orders 
	WHERE YEAR(OrderCompletedDate) = YEAR(GETDATE()) 
	)StateMonthDetail
GROUP BY OrderState, OrderYear

Open in new window

0
 
EGormlyAuthor Commented:
Aside from being a Genius and working PERFECTLY can I ask you where I can learn some of this magical SQL?
0
 
RPCITCommented:
I learned from google... google knows everything..

Actually .. you just see different things.. and then learn to put them together.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SharathData EngineerCommented:
can you provide some sample data. if possible fo for PIVOT.
0
 
SharathData EngineerCommented:
*fo  = go
0
 
EGormlyAuthor Commented:
exellent answer!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now