Solved

SQL syntax question

Posted on 2013-10-22
6
288 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Union 20 44
subtract 1 in Access 2003 query 7 37
Removing SQL Replication from Microsoft SQL Server 2008 R2 2 14
grouping logic 6 47
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

23 Experts available now in Live!

Get 1:1 Help Now