CODEWITHSUNDEEP
sql
David Gabbay
David Gabbay

Reputation: 117

Monthly sales as column

SQL question, making monthly and yearly sales as column:

I have two tables:

Columns are:

I want to get the following columns:

item_num | item_name | currnt_stock | QTY sold 2018 | QTY sold:Jan2018 | Feb 2018 | March 2018 .... Dec 2018

For now I used this query but it gives me only the total sales of each item:

SELECT 
    i.item_num AS ItemNum, 
    i.item_name AS ItemName, 
    sum(d.itm_qnt_out-d.itm_qnt_in) AS QTOUT,
    i.itm_qnt AS Stock
FROM 
    dbo.t_items AS i
FULL OUTER JOIN  
    t_items_dtl AS d ON i.item_num = d.item_num
GROUP BY 
    i.item_num, i.item_name, i.itm_qnt;

How can I add monthly and yearly sales by column (not rows)?

Upvotes: 1

Views: 618

Answers (1)

FXD
FXD

Reputation: 2060

Depending on the database you are using there may be more elegant ways to get year and month from a date but bottom-line, you probably want something like:

SELECT 
    i.item_num AS ItemNum, 
    i.item_name AS ItemName, 
    sum(d.itm_qnt_out-d.itm_qnt_in) AS QTOUT,
    sum(CASE WHEN to_char(invdate,'mm') = '01' THEN d.itm_qnt_out-d.itm_qnt_in ELSE 0 END) AS QTOUTJan,
    sum(CASE WHEN to_char(invdate,'mm') = '02' THEN d.itm_qnt_out-d.itm_qnt_in ELSE 0 END) AS QTOUTFeb,
    sum(CASE WHEN to_char(invdate,'mm') = '03' THEN d.itm_qnt_out-d.itm_qnt_in ELSE 0 END) AS QTOUTMar,
/*Repeat for every month*/
    i.itm_qnt AS Stock
FROM 
    dbo.t_items AS i
FULL OUTER JOIN  
    t_items_dtl AS d ON i.item_num = d.item_num
WHERE to_char(invdate, 'yyyy') = '2018'

GROUP BY 
    i.item_num, i.item_name, i.itm_qnt;

Compared to your code, I have defined a column for each month + added a WHERE to filter the entire thing to 2018.

Upvotes: 1

Related Questions