Surendar S Surendar S - 4 months ago 9
SQL Question

How to add months in select query

Hi here is the query where I get data from oracle

Select EXTRACT(YEAR FROM A.date_applied) Year,
EXTRACT(Month FROM A.date_applied) Month,
round(sum(nvl(decode(A.direction,'+',(A.quantity-a.qty_reversed)*A.cost),0))) Value
From INVENTORY_TRANSACTION_HIST2 a
group by EXTRACT(Month FROM A.date_applied),
EXTRACT(YEAR FROM A.date_applied)
order by EXTRACT(Year FROM A.date_applied);


OUTPUT :

enter image description here

But I want the output as follows if there is no data available for the particular month it should display as 0.

If there is no data available for a month it should add the month and display the output as 0 from that month

Answer

You missed the output part, but I assume you are missing some months data. You can take this and adjust it. First join to a derived table containing all months , and left join to your query:

SELECT t.mm,t.year,
        round(sum(nvl(decode(A.direction,'+',(A.quantity-a.qty_reversed)*A.cost),0))) Value
FROM
    (SELECT 1 as mm,2015 as year FROM DUAL
     UNION ALL
     SELECT 2 as mm,2015 as year FROM DUAL
     UNION ALL
     ....) t -- The range of the months you want
LEFT OUTER JOIN INVENTORY_TRANSACTION_HIST2 a
 ON(t.mm = EXTRACT(month from a.date_applied) AND t.year = EXTRACT(year from a.date_applied))
group by t.mm,t.year
order by t.year,t.mm;
Comments