Select query to results all 12 months even if data not exist

Question

I'm trying to fetch out all month names with the value of zero if data does not exist.

Here is what I achieved so far

invoice_order table

order_id | user_id    | order_date | order_total_amount
1        | 1          | 01-01-2021 | 10000
2        | 1          | 02-02-2021 | 20000

The SQL query would pick the order_total_amount of the first row (10000) which would store in January month. but for February, it is showing order_total_amount zero

value zero needs to show up only when no data exists

$monthly_sale = "SELECT 
    SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
    SUM(IF(month = 'May', total, 0)) AS 'May',
    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
    SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
    FROM (
SELECT DATE_FORMAT(order_date,'%M') AS month, SUM(order_total_amount) as total
FROM invoice_order
WHERE user_id='$user_id' group by year(order_date),month(order_date) order by year(order_date),month(order_date)) as sale";

the above query would return all month names along with zero value (if data doesn't exist). but it showing order_total_amount of only the first row. To make it more simplified, it picking up the order amount of only one month

nbk · Accepted Answer · 2021-05-05T17:30:42.657

You have a type you need date_FORMAt &b for your query

But your code is vulnerable to sql injection so instead of inserting the $user_id use prepared statements with parameters see How can I prevent SQL injection in PHP?

CREATE TABLE invoice_order (user_id int,order_date date, order_total_amount DECIMAL(10,2))

✓

INSERT INTO invoice_order VALUES 
(1,'2020-01-01',10.1),(1,'2020-02-01',10.1),(1,'2020-03-01',10.1),
(1,'2020-01-04',10.1),(1,'2020-05-01',10.1),(1,'2020-06-01',10.1),
(1,'2020-07-01',10.1),(1,'2020-08-01',10.1),(1,'2020-09-01',10.1),
(1,'2020-10-01',10.1),(1,'2020-11-01',10.1),(1,'2020-12-01',10.1),
(1,'2021-01-01',10.1),(1,'2021-02-01',10.1),(1,'2021-03-01',10.1)

SELECT 
        MIN(DATE_FORMAT(order_date, '%b')) AS month,
            SUM(order_total_amount) AS total
    FROM
        invoice_order
    WHERE
        user_id = '1'
    GROUP BY  MONTH(order_date)
    ORDER BY MONTH(order_date)

month | total
:---- | ----:
Jan   | 30.30
Feb   | 20.20
Mar   | 20.20
May   | 10.10
Jun   | 10.10
Jul   | 10.10
Aug   | 10.10
Sep   | 10.10
Oct   | 10.10
Nov   | 10.10
Dec   | 10.10

SELECT 
    SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
    SUM(IF(month = 'May', total, 0)) AS 'May',
    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
    SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM
    (SELECT 
        MIN(DATE_FORMAT(order_date, '%b')) AS month,
            SUM(order_total_amount) AS total
    FROM
        invoice_order
    WHERE
        user_id = '1'
    GROUP BY YEAR(order_date) , MONTH(order_date)
    ORDER BY YEAR(order_date) , MONTH(order_date)) AS sale

  Jan |   Feb |   Mar |  Apr |   May |   Jun |   Jul |   Aug |   Sep |   Oct |   Nov |   Dec
----: | ----: | ----: | ---: | ----: | ----: | ----: | ----: | ----: | ----: | ----: | ----:
30.30 | 20.20 | 20.20 | 0.00 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10 | 10.10

db<>fiddle here

Unfortunately, it didn't work! Now it is not even picking up the value ($order_total_amount) of the first row — Shakti Goyal, May 05 '21 at 17:17
Maybe you misunderstood my question. I updated the question. Can you check the question again @nbk — Shakti Goyal, May 05 '21 at 17:27
yes it is bcause it is varchar an int 0 would not be displayed — nbk, May 05 '21 at 18:02
Got it! So I just have to change the datatype from varchar to int! Thank you — Shakti Goyal, May 05 '21 at 18:06
Just changed the data type! But it is still showing the bar despite having 0 value! Weird — Shakti Goyal, May 05 '21 at 18:09

score 0 · Answer 2 · answered May 05 '21 at 17:13

I would suggest writing this as:

SELECT year(order_date),
       SUM(CASE WHEN MONTH(order_date) = 1 THEN order_total_amount ELSE 0 END) as JAN,
       SUM(CASE WHEN MONTH(order_date) = 2 THEN order_total_amount ELSE 0 END) as FEB,
       . . . 
       SUM(CASE WHEN MONTH(order_date) = 12 THEN order_total_amount ELSE 0 END) as DEC
FROM invoice_order ii
WHERE user_id = ?
GROUP BY year(order_date)
ORDER BY year(order_date);

Notes:

There is no need for a subquery.
There is a handy MONTH() function which returns the month number.
Don't put column aliases in single quotes. Only use single quotes for date and time constants.
Pass the user id in as a parameter. Don't munge query strings with constant values.

KeithL · Answer 3 · 2021-05-05T18:17:09.487

UPDATE: Sorry did not see the MySQL tag. This is a SQL Server solution. I will leave for that intended use case.

I like using a cross apply with a numbers table (i chose a while loop for ease to create this) for something like this.

create table #t(m int)

create table #invoiceOrder(orderDate date, order_total_amount money)

insert into #invoiceOrder
values
('1/1/2020' , 10.5)

declare @i int = 1
while (@i<13)
begin 
    insert into #t values(@i)
    select @i=@i+1
end

select m,sum(case when month(io.orderDate)=m then io.order_total_amount else 0 end )
from #invoiceOrder io
cross apply (select m from #t) t
group by m

drop table #t,#invoiceOrder

The question appears to be about MySQL – Strawberry May 05 '21 at 17:53 — Strawberry, May 05 '21 at 17:53

Select query to results all 12 months even if data not exist

3 Answers3

Linked