MySQL Query for Graphs

Question

I've got the following query:

SELECT GVA12.FECHA_EMIS, GVA12.COD_VENDED, sum(GVA12.IMPORTE)
FROM GVA12
WHERE Month(GVA12.FECHA_EMIS)=Month(curDate())
  AND Year(GVA12.FECHA_EMIS)=Year(curDate())
  AND GVA12.COD_VENDED="EX"
  AND GVA12.T_COMP="FAC"
GROUP BY GVA12.FECHA_EMIS

This is for a monthly graph. I've got two questions. One, how can I show all the dates of the months as zero (the ones that don't have any sales), and two, is there any way to make the values go adding up, so the last value is the total of all the values.

Edit: @Bluefeet with your query, I created the following,

SELECT Month(Days.DMY), Year(Days.DMY), GVA12.COD_VENDED, sum(GVA12.IMPORTE)
FROM Days
left join GVA12
    on Month(Days.DMY) = Month(GVA12.FECHA_EMIS)
    and Year(Days.DMY) = Year(GVA12.FECHA_EMIS)
WHERE Month(GVA12.FECHA_EMIS)=Month(curDate())
  AND Year(GVA12.FECHA_EMIS)=Year(curDate())
  AND GVA12.COD_VENDED="EX"
  AND GVA12.T_COMP="FAC"
GROUP BY Month(Days.DMY), Year(Days.DMY) WITH ROLLUP

I got the result attached (screenshot).

It doesn't show all the days of the month as I wanted. What can I do?

Edit #3

It works now, but I want to add another filter. This filter is added here http://sqlfiddle.com/#!2/9d46c/1

Answer 1

To get the total you can use the GROUP BY WITH ROLLUP which should give you the Total of all dates:

SELECT GVA12.FECHA_EMIS, GVA12.COD_VENDED, sum(GVA12.IMPORTE)
FROM GVA12
WHERE Month(GVA12.FECHA_EMIS)=Month(curDate())
  AND Year(GVA12.FECHA_EMIS)=Year(curDate())
  AND GVA12.COD_VENDED="EX"
  AND GVA12.T_COMP="FAC"
GROUP BY GVA12.FECHA_EMIS WITH ROLLUP

As far as returning dates that do not exist, There are many questions on SO that answer that including the following. Sometimes it is easier in MySQL to create a table to join on:

generate days from date range

Get a list of dates between two dates

Edit #1: if you have a table with dates, then you could use something similar to this:

SELECT Month(d.yourDateCol), Year(d.yourDateCol), g.COD_VENDED, sum(g.IMPORTE)
FROM dates d
left join GVA12 g
    on Month(d.yourDateCol) = Month(GVA12.FECHA_EMIS)
    and Year(d.yourDateCol) = Year(GVA12.FECHA_EMIS)
WHERE Month(g.FECHA_EMIS)=Month(curDate())
  AND Year(g.FECHA_EMIS)=Year(curDate())
  AND g.COD_VENDED="EX"
  AND g.T_COMP="FAC"
GROUP BY Month(d.yourDateCol), Year(d.yourDateCol) WITH ROLLUP

Edit #2: Without seeing your full table structure or some sample data, here is a version of the query that is working:

select month(d.dmy) Month, 
  year(d.dmy) Year, 
  coalesce(sum(g.Importe), 0) TotalImporte
from dates d
left join GVA12 g
  on month(d.dmy) = month(g.FECHA_EMIS)
  and year(d.dmy) = year(g.FECHA_EMIS)
group by month(d.dmy), year(d.dmy)  WITH ROLLUP

See SQL Fiddle with Demo. This returns the month/year for each month/year in the dates table even if it does not exist in the GVA12 table,

Edit #3: If you want the running total, not just the final total, then you should be able to use the following:

SET @running_total := 0;

SELECT month(Days.DMY) Month, 
  Year(Days.DMY) Year, 
  Date(Days.DMY) Date,
  g.COD_VENDED,
  @running_total := @running_total + Coalesce(TotalImport, 0) as TotalImport
FROM Days
left join
(
  select FECHA_EMIS,
    COD_VENDED,
    sum(IMPORTE) TotalImport
  from GVA12
  group by Date(FECHA_EMIS), Year(FECHA_EMIS)
) g
  on date(Days.DMY) = date(g.FECHA_EMIS)
  and g.COD_VENDED='EX'
  and Month(g.FECHA_EMIS)=Month(curDate())
  and Year(g.FECHA_EMIS)=Year(curDate())
WHERE month(days.dmy)=Month(curDate())

See SQL Fiddle with Demo

The result is:

| MONTH | YEAR |                           DATE | COD_VENDED | TOTALIMPORT |
----------------------------------------------------------------------------
|     1 | 2013 | January, 01 2013 00:00:00+0000 |     (null) |           0 |
|     1 | 2013 | January, 02 2013 00:00:00+0000 |         EX |        1000 |
|     1 | 2013 | January, 03 2013 00:00:00+0000 |         EX |        4000 |
|     1 | 2013 | January, 04 2013 00:00:00+0000 |     (null) |        4000 |
|     1 | 2013 | January, 05 2013 00:00:00+0000 |     (null) |        4000 |
|     1 | 2013 | January, 06 2013 00:00:00+0000 |     (null) |        4000 |
|     1 | 2013 | January, 07 2013 00:00:00+0000 |     (null) |        4000 |

Answer 2

a couple ideas:

one - you need some data values to compare against - so you could build a new table to hold all the dates - statically - then you do an outer join to that to make sure you get the zeroes.

two - i'm not sure about mysql - but in Oracle this is a LAG function. maybe that is a helpful pointer for further research.

Answer 3

Have a look at this link, there is explained how to list all dates between 2 dates Get a list of dates between two dates

you can list using the following but I think there are better solution in the above link:

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2012-12-01' and '2012-12-31';