SQL syntax question

Here is my table

Table:SALES            
PRODUCT_ID    SALEDATE      SALEAMOUNT
1          1-Feb-11      100
1          5-Feb-11      10
1          1-Mar-11      50
1          4-Mar-11      10
2          6-Mar-11      200
3          8-Mar-11      500
4          9-Aug-11      700


script:
create table SALES (PRODUCT_ID int,  SALEDATE datetime ,SALEAMOUNT money)
go
INSERT INTO t_PRODUCTSALES VALUES (1, '1-Feb-11', 100)
INSERT INTO t_PRODUCTSALES VALUES (1, '5-Feb-11', 10)
INSERT INTO t_PRODUCTSALES VALUES (1, '1-Mar-11', 50)
INSERT INTO t_PRODUCTSALES VALUES (1, '4-Mar-11', 10)
INSERT INTO t_PRODUCTSALES VALUES (2, '6-Mar-11', 200)
INSERT INTO t_PRODUCTSALES VALUES (3, '8-Mar-11', 500)
INSERT INTO t_PRODUCTSALES VALUES (4, '9-Aug-11', 700)

Using the Sales table , I need to write a query to show the sum of SALEAMOUNT for each PRODUCT_ID against each month name in 2011

So the results should look like the attached pic...                                                                                          
Thanks for the help
jknj72Asked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Cleaned up, with drop/create
IF OBJECT_ID('SALES') IS NOT NULL
   DROP TABLE SALES
GO

CREATE TABLE SALES (PRODUCT_ID int, SALEDATE datetime, SALEAMOUNT money)
GO

INSERT INTO SALES  (PRODUCT_ID, SALEDATE, SALEAMOUNT)
VALUES 
   (1, '1-Feb-11', 100), (1, '5-Feb-11', 10), (1, '1-Mar-11', 50),
   (1, '4-Mar-11', 10),  (2, '6-Mar-11', 200), (3, '8-Mar-11', 500),
   (4, '9-Aug-11', 700)

SELECT Product_ID, DateName(month, SALEDATE), Sum(SaleAmount) as sales_sum
FROM SALES
WHERE YEAR(SALEDATE) = 2011
GROUP BY Product_ID, DateName(month, SALEDATE)
ORDER BY Product_ID, DateName(month, SALEDATE)

Open in new window

btw there is no picture in this question
0
 
jknj72Author Commented:
Sorry the script says t_PRODUCTSALES should be below

create table SALES (PRODUCT_ID int,  SALEDATE datetime ,SALEAMOUNT money)
go
INSERT INTO SALES VALUES (1, '1-Feb-11', 100)
INSERT INTO SALES VALUES (1, '5-Feb-11', 10)
INSERT INTO SALES VALUES (1, '1-Mar-11', 50)
INSERT INTO SALES VALUES (1, '4-Mar-11', 10)
INSERT INTO SALES VALUES (2, '6-Mar-11', 200)
INSERT INTO SALES VALUES (3, '8-Mar-11', 500)
INSERT INTO SALES VALUES (4, '9-Aug-11', 700)
0
 
jknj72Author Commented:
Also, The pic also doesnt have Feb in it for some reason. I think that is an error?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
jknj72Author Commented:
sorry here is the pic. Not sure if that will change anything though

thanks
Results.jpg
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What you are asking for is a PIVOT, with PRODUCT_ID as rows, the twelve months as columns, and the sum of sales for each PRODUCT_ID - month combination in the intersecting 'cell'.
SELECT
    Product_ID,
    [1] AS Jan, [2] AS Feb, [3] AS Mar,
    [4] AS Apr, [5] AS May, [6] AS Jun,
    [7] AS Jul, [8] AS Aug, [9] AS Sep,
    [10] AS Oct, [11] AS Nov, [12] AS Dec
FROM (
SELECT Product_ID, MONTH(SALEDATE) as the_month, SaleAmount
FROM SALES
WHERE YEAR(SALEDATE) = 2011) src
PIVOT
(
    Sum(SaleAmount)
    FOR the_month
    IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) AS pvt

Open in new window

0
 
jknj72Author Commented:
Thanks JimHorn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.