CODEWITHSUNDEEP
mysqlsql
John Cargo
John Cargo

Reputation: 2121

Fetching Total Sale of each day and month from database

I have table with column c_date as datetime, total as int type in mysql, and i want to print out sale of each day, and total sale of each month, and total sale annually including day, month, year where there was no sale.

Currently for daily sale, I am running below query :

mysql> select date(c_date) as date, sum(total) as total_sale from sale group by date;
+------------+------------+
| date       | total_sale |
+------------+------------+
| 2013-10-3  |        798 |
| 2013-10-6  |        114 |
+------------+------------+

but, i want something like this :

mysql> select date(c_date) as date, sum(total) as total_sale from sale group by date;
+------------+------------+
| date       | total_sale |
+------------+------------+
| 2013-10-1  |          0 |
| 2013-10-2  |          0 |
| 2013-10-3  |        798 |
| 2013-10-4  |          0 |
| 2013-10-5  |          0 |
| 2013-10-6  |        114 |
+------------+------------+

and for Monthly, I am getting this :

mysql> select c_date, month(c_date) as month, year(c_date) as year, sum(total) as total from sale group by c_date order by c_date;
+---------------------+-------+------+-------+
| c_date              | month | year | total |
+---------------------+-------+------+-------+
| 2013-10-3 02:40:06  |    10 | 2013 |   228 |
| 2013-10-3 02:41:58  |    10 | 2013 |   114 |
| 2013-10-3 02:44:36  |    10 | 2013 |   114 |
| 2013-10-3 02:46:40  |    10 | 2013 |   114 |
| 2013-10-3 02:49:15  |    10 | 2013 |   114 |
| 2013-10-3 02:53:36  |    10 | 2013 |   114 |
| 2013-10-6 07:43:27  |    10 | 2013 |   114 |
+---------------------+-------+------+-------+

But i want something like this :

mysql> select c_date, month(c_date) as month, year(c_date) as year, sum(total) as total from sale group by c_date order by c_date;
+---------------------+-------+------+-------+
| c_date              | month | year | total |
+---------------------+-------+------+-------+
| 2013-1-3 02:40:06   |     1 | 2013 |     0 |
| 2013-2-3 02:41:58   |     2 | 2013 |     0 |
| 2013-3-3 02:44:36   |     3 | 2013 |     0 |
| 2013-4-3 02:46:40   |     4 | 2013 |     0 |
| 2013-5-3 02:49:15   |     5 | 2013 |     0 |
| 2013-6-3 02:53:36   |     6 | 2013 |     0 |
| 2013-7-6 07:43:27   |     7 | 2013 |     0 |
| 2013-8-3 02:44:36   |     8 | 2013 |     0 |
| 2013-9-3 02:46:40   |     9 | 2013 |     0 |
| 2013-10-3 02:49:15  |    10 | 2013 |   912 |
| 2013-11-3 02:53:36  |    11 | 2013 |     0 |
| 2013-12-6 07:43:27  |    12 | 2013 |     0 |
+---------------------+-------+------+-------+

Is this possible with MysqL ?

Upvotes: 2

Views: 4223

Answers (2)

mucio
mucio

Reputation: 7119

it looks to me that you need an outer join with a calendar table.

Imagine a calendar table populated like:

Calendar

Year Month   Day
2013 201310  2013-10-1    
2013 201310  2013-10-2
...

Then you can write a query like 

         select date(c_day) as date, 
                sum(total) as total_sale 
           from calendar c 
left outer join sale s 
             on c.day = s.c_date
          where c.month = 201310
       group by c_day
         having c_day <= max(s.c_date); -- this is to avoid to show all 
                                        -- days for October

Upvotes: 1

Alma Do
Alma Do

Reputation: 37365

Since it's impossible to use sequences in MySQL (actually, they simply do not exist there), you'll have to create your dates range table first. That will be like:

CREATE TABLE dates_range (record_date DATE)

and then fill this table with dates, starting from minimum among dates, that exist in your sale table and till maximum.

After this, using SQL LEFT JOIN operator, you'll be able to aggregate your data like this:

SELECT
  YEAR(dates_range.record_date),
  MONTH(dates_range.record_date),
  DAY(dates_range.record_date),
  COALESCE(SUM(sale.total), 0) AS total_sum
FROM
  dates_range
    LEFT JOIN sale
      ON dates_range.record_date=DATE(sale.c_date)
GROUP BY
  YEAR(dates_range.record_date),
  MONTH(dates_range.record_date),
  DAY(dates_range.record_date)

Upvotes: 1

Related Questions