CODEWITHSUNDEEP
mysql
Lakmal Premaratne
Lakmal Premaratne

Reputation: 1229

mysql running difference with group by

Dataset I am experimenting has the structure as given in this SQLFiddle. 

create table readings_tab (id int, site varchar(15), logged_at datetime, reading smallint);

insert into readings_tab values (1, 'A', '2017-08-21 13:22:00', 2500);
insert into readings_tab values (2, 'B', '2017-08-21 13:22:00', 1210);
insert into readings_tab values (3, 'C', '2017-08-21 13:22:00', 3500);
insert into readings_tab values (4, 'A', '2017-08-22 13:22:00', 2630);
insert into readings_tab values (5, 'B', '2017-08-22 13:22:00', 1400);
insert into readings_tab values (6, 'C', '2017-08-22 13:22:00', 3800);
insert into readings_tab values (7, 'A', '2017-08-23 13:22:00', 2700);
insert into readings_tab values (8, 'B', '2017-08-23 13:22:00', 1630);
insert into readings_tab values (9, 'C', '2017-08-23 13:22:00', 3950);
insert into readings_tab values (10, 'A', '2017-08-24 13:22:00', 2850);
insert into readings_tab values (11, 'B', '2017-08-24 13:22:00', 1700);
insert into readings_tab values (12, 'C', '2017-08-24 13:22:00', 4200);
insert into readings_tab values (13, 'A', '2017-08-25 13:22:00', 3500);
insert into readings_tab values (14, 'B', '2017-08-25 13:22:00', 2300);
insert into readings_tab values (15, 'C', '2017-08-25 13:22:00', 4700);

Current Query:

select t.rownum, t.logged_on, t.tot_reading, coalesce(t.tot_reading - t3.tot_reading, 0) AS daily_generation
from 
  (
    select @rn:=@rn+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t, (SELECT @rn:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t 
  left join 
  (
    select @rn:=@rn+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t, (SELECT @rn:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t3 on t.rownum = t3.rownum + 1
  order by t.logged_on desc;

I am expecting below output. I don't need the formula (3500+2300+4700, etc...) in the result set. Just included it to make it understandable.

-----------------------------------------------------------------
| logged_on  |      tot_reading         |    daily_generation   |
-----------------------------------------------------------------
| 2017-08-25 | (3500+2300+4700) = 10500 | (10500 - 8750) = 1750 |
| 2017-08-24 | (2850+1700+4200) =  8750 |    (8750-8280) =  470 |
| 2017-08-23 | (2700+1630+3950) =  8280 |    (8280-7830) =  450 |
| 2017-08-22 | (2630+1400+3800) =  7830 |    (7830-7210) =  620 |
| 2017-08-21 | (2500+1210+3500) =  7210 |                     0 |
-----------------------------------------------------------------

I cannot figure out why it doesn't produce expected output. Can someone please help?

Upvotes: 1

Views: 37

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

If using variables make sure they are unique to each subquery else you can get incorrect results. I suggest the following adjusted query (which has some added columns to help follow what is happening):

select
      t.rownum, t.logged_on, t.tot_reading
    , coalesce(t.tot_reading - t3.tot_reading, 0) AS daily_generation
    , t3.rownum t3_rownum
    , t3.tot_reading t3_to_read
    , t.tot_reading  t_tot_read
from 
  (
    select @rn:=@rn+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t
    cross join (SELECT @rn:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t 
  left join 
  (
    select @rn2:=@rn2+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t
    cross join (SELECT @rn2:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t3 on t.rownum = t3.rownum + 1
  order by t.logged_on desc
  ;

Note I also recommend using explicit CROSS JOIN syntax as it leads to easier comprehension for anyone who needs to maintain this query.

Here is the result (& also see http://sqlfiddle.com/#!9/dcb5e2/1 )

| rownum |  logged_on | tot_reading | daily_generation | t3_rownum | t3_to_read | t_tot_read |
|--------|------------|-------------|------------------|-----------|------------|------------|
|      5 | 2017-08-25 |       10500 |             1750 |         4 |       8750 |      10500 |
|      4 | 2017-08-24 |        8750 |              470 |         3 |       8280 |       8750 |
|      3 | 2017-08-23 |        8280 |              450 |         2 |       7830 |       8280 |
|      2 | 2017-08-22 |        7830 |              620 |         1 |       7210 |       7830 |
|      1 | 2017-08-21 |        7210 |                0 |    (null) |     (null) |       7210 |

Upvotes: 1

Related Questions