SQL Statement for Grouping orders from 50 States

Posted on 2009-04-27
Last Modified: 2012-05-06
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?

Question by:EGormly
    LVL 6

    Accepted Solution

    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()) 
    GROUP BY OrderState, OrderYear

    Open in new window


    Author Comment

    Aside from being a Genius and working PERFECTLY can I ask you where I can learn some of this magical SQL?
    LVL 6

    Expert Comment

    I learned from google... google knows everything..

    Actually .. you just see different things.. and then learn to put them together.
    LVL 40

    Expert Comment

    can you provide some sample data. if possible fo for PIVOT.
    LVL 40

    Expert Comment

    *fo  = go

    Author Closing Comment

    exellent answer!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    results of a query into a new table into sql 5 40
    IF SQL Statement Access SQL 6 38
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now