CODEWITHSUNDEEP
mysqlsql
Arslan
Arslan

Reputation: 179

How to create a MySQL Query to get sales for each date of month (Like Calendar)?

I have three tables: Product, Invoice and Invoice_Detail
enter image description here

I want to generate a monthly sales report for each date of a given month as shown in figure below. enter image description here

I have tried many queries with different joins but I am not getting the desired output. How can I achieve the desired monthly sales report with a query as shown in figure 2?

Thanks

Upvotes: 1

Views: 672

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use conditional aggregation:

select p.p_name,
       sum(case when day(i.i_date) = 1 then id.id_quantity else 0 end) as day_01,
       sum(case when day(i.i_date) = 1 then id.id_quantity else 0 end) as day_02,
       . . .  -- fill in for the rest of the days
       sum(case when day(i.i_date) = 1 then id.id_quantity else 0 end) as day_31
from invoice i join
     invoice_detail id
     on id.id_invoice = i.i_id
     product p
     on id.id_product = p.p_id
where i.i_date >= '2019-08-01' and
      i.i_date < '2019-09-01'
group by p.p_name;

The only real things to note is the filtering on the dates, so you have data for only one month and use of day() for the day of the month.

Upvotes: 1

Related Questions