Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL syntax question

Posted on 2013-10-22
6
Medium Priority
?
298 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 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

610 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