CODEWITHSUNDEEP
mysqlsql
Herfox
Herfox

Reputation: 137

SQL, difficult fetching data query

Suppose I have such a table:

+-----+---------+-------+
| ID  | TIME    | DAY   |
+-----+---------+-------+
|  1  |    1    |   1   |
|  2  |    2    |   1   |
|  3  |    3    |   1   |
|  1  |    1    |   2   |
|  2  |    2    |   2   |
|  3  |    3    |   2   |
|  1  |    1    |   3   |
|  2  |    2    |   3   |
|  3  |    3    |   3   |
|  1  |    1    |   4   |
|  2  |    2    |   4   |
|  3  |    3    |   4   |
|  1  |    1    |   5   |
|  2  |    2    |   5   |
|  3  |    3    |   5   |
+-----+---------+-------+

I want to fetch a table which represents 2 IDs which got the largest sum of TIME within the last 3 days (means from 3 to 5 in a DAY column)

So the correct result would be:

+-----+---------+
| ID  | SUM     |
+-----+---------+
|  3  |    9    |
|  2  |    6    |
+-----+---------+

The original table is much larger and more complex. So i need a generic approach.

Thanks in advance.

Upvotes: 0

Views: 57

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9129

And so I just learned that MySQL used LIMIT instead of TOP...

fiddle

CREATE TABLE tbl (ID INT,tm INT,dy INT);

INSERT INTO tbl (id, tm, dy) VALUES
 (1,1,1)
,(2,2,1)
,(3,3,1)
,(1,1,2)
,(1,1,1)


SELECT ID
      ,SUM(SumTimeForDay) SumTimeFromLastThreeDays 
  FROM (SELECT ID
              ,SUM(tm) SumTimeForDay
          FROM tbl
         GROUP BY ID, dy
        HAVING dy > MAX(dy) -3) a
 GROUP BY id
 ORDER BY SUM(SumTimeForDay) DESC
 LIMIT 2

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

select t1.`id`, sum(t1.`time`) as `sum` 
from `table` t1
inner join ( select distinct `day` from `table` order by `day` desc limit 3 ) t2
      on t2.`da`y = t1.`day` 
group by t1.`id`
order by sum(t1.`time`) desc
limit 2

Upvotes: 0

Related Questions