Query showing the amount per day just with views

Question

I have these registers in mysql (also in postgresql):

Venta   
date        amount
01/02/2015  7
10/02/2015  8
10/02/2015  9
20/03/2015  4
20/03/2015  11
25/04/2015  1

So, I would like to make a query where I get the total amount per day and if an amount in the day doesn't exist, display the day with 0. Something like this:

date        amount
01/01/2015  0
02/01/2015  0
03/01/2015  0
04/01/2015  0
05/01/2015  0
06/01/2015  0
.  .   .    .
.  .   .    .
01/02/2015  7
.  .   .    .
.  .   .    .
10/02/2015  17
.  .   .    .
.  .   .    .
20/03/2015  15
.  .   .    .
.  .   .    .
25/04/2015  1
.  .   .    .
.  .   .    .
30/12/2015  0
31/12/2015  0

How can I make the query without using function? I heard that views are the best way, but I have not idea how to make it.

You need a table with the dates. Then it's a simple outer join. — mdahlman, May 09 '15 at 04:07

score 3 · Answer 1 · answered May 09 '15 at 02:14

3

You can do this readily in Postgres:

select dates.dte, coalesce(sum(v.amount), 0) as amount
from generate_series('2015-01-01'::timestamp, '2015-12-31'::timestamp, '1 day'::interval) dates(dte) left join
     venta v
     on v.date = dates.dte
group by dates.dte
order by dates.dte;

This is much more cumbersome in MySQL, unless you have a calendar table.

answered May 09 '15 at 02:14

Gordon Linoff

1,242,037
58
646
786

2

Thanks for your answer, but you are using generate_series which is a special function from postgresql.. the idea is do it without a function like that – Natali Torres May 09 '15 at 02:19
[Uncle Google could help you](http://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql) – klin May 09 '15 at 04:09

score 0 · Answer 2 · answered May 09 '15 at 14:16

0

Create a calender table with all date you need, and do the join.

answered May 09 '15 at 14:16

Tim3880

2,563
1
11
14

TommCatt · Answer 3 · 2015-05-10T04:48:12.517

Instead of a table with a list of sequential dates, just define what has been called a number or tally table. It is simply a one-column table of sequential integer values from 1 or 0 on up to some ridiculous limit. This table will then provide a way to sequence through letters of the alphabet or dates in a range or indexes into a varchar or whatever you need.

I call mine Tally and the one field is Num and the values start at zero instead of one.

select  t.Num
from    Tally t
where   t.Num between 0 and 6;

This supplies seven consecutive values or a week's worth of dates. All you need to do is set the starting point.

select  t.Num, StartDate + t.Num DateValue
from    Tally t
where   t.Num between 0 and 6;

Now join that with your table and group accordingly.

select  StartDate + t.Num VentaDate, Coalesce( Sum( m.Amount ), 0 ) DailyAmount 
from    Tally t
left join MyTable m
    on  m.Venta = StartDate + t.Num
group by StartDate + t.Num
where   t.Num between 0 and 6;

Query showing the amount per day just with views

3 Answers3