Select Query To Results All 12 Months Even If Data Not Exist

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

Solution 1:

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?

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

✓

INSERTINTO 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)

SELECTMIN(DATE_FORMAT(order_date, '%b')) ASmonth,
            SUM(order_total_amount) AS total
    FROM
        invoice_order
    WHERE
        user_id ='1'GROUPBYMONTH(order_date)
    ORDERBYMONTH(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

SELECTSUM(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
    (SELECTMIN(DATE_FORMAT(order_date, '%b')) ASmonth,
            SUM(order_total_amount) AS total
    FROM
        invoice_order
    WHERE
        user_id ='1'GROUPBYYEAR(order_date) , MONTH(order_date)
    ORDERBYYEAR(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

Solution 2:

I would suggest writing this as:

SELECTyear(order_date),
       SUM(CASEWHENMONTH(order_date) =1THEN order_total_amount ELSE0END) as JAN,
       SUM(CASEWHENMONTH(order_date) =2THEN order_total_amount ELSE0END) as FEB,
       . . . 
       SUM(CASEWHENMONTH(order_date) =12THEN order_total_amount ELSE0END) asDECFROM invoice_order ii
WHERE user_id = ?
GROUPBYyear(order_date)
ORDERBYyear(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.

Solution 3:

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.

createtable #t(m int)

createtable #invoiceOrder(orderDate date, order_total_amount money)

insertinto #invoiceOrder
values
('1/1/2020' , 10.5)

declare@iint=1
while (@i<13)
begininsertinto #t values(@i)
    select@i=@i+1endselect m,sum(casewhenmonth(io.orderDate)=m then io.order_total_amount else0end )
from #invoiceOrder io
cross apply (select m from #t) t
groupby m

droptable #t,#invoiceOrder