Solved

SQL syntax question

Posted on 2013-10-22
6
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 66

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

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

thanks
Results.jpg
0
 
LVL 66

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

707 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