Solved

SQL syntax question

Posted on 2013-10-22
6
292 Views
Last Modified: 2013-10-22
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
0
Comment
Question by:jknj72
  • 4
  • 2
6 Comments
 

Author Comment

by:jknj72
ID: 39591332
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
 

Author Comment

by:jknj72
ID: 39591337
Also, The pic also doesnt have Feb in it for some reason. I think that is an error?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39591384
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:jknj72
ID: 39591520
sorry here is the pic. Not sure if that will change anything though

thanks
Results.jpg
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39591649
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
 

Author Closing Comment

by:jknj72
ID: 39591653
Thanks JimHorn
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

828 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