Mysql – Sum duration for multiple records

MySQL

I have a table with fallowing columns:

id  device_id (string) used_at (datetime) app_id (string) duration (int)
1   device-1           2018-07-20         chrome          30
2   device-1           2018-07-19         chrome          1
3   device-2           2018-07-19         chrome          2
4   device-2           2018-07-20         facebook        100
5   device-1           2018-07-20         chrome          20
6   device-3           2018-07-20         chrome          10
7   device-1           2018-07-20         facebook        5

I need to get list of ten most used app_id for specified device-1 and used_at this list should have duration sum

So I should get for device-1 and uset_at >= 2018-07-18:

device-1 chrome duration (51)
device-1 facebook duration (5)

Anyone can suggest sql ?

Best Answer

I am not exactly sure what you are asking for in this post.

Here is what I came up with

CREATE TABLE device_table
(
    id INT PRIMARY KEY NOT NULL auto_increment
  , defice_id VARCHAR(20)
  , used_at DATETIME
  , app_id VARCHAR(30)
  , duration INT
);

INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 30);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-19', 'chrome', 1);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-19', 'chrome', 2);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-20', 'facebook', 100);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 20);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-3', '2018-07-20', 'chrome', 10);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'facebook', 5);

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(SUM(duration) AS CHAR), ')') 
FROM device_table
-- remove filter to include everything else
WHERE device_id = 'device-1'
-- if you want top ten for "device-1" then comment out the date filter
AND   used_at >= '2018-07-18'
GROUP BY device_id
       , app_id
-- and uncomment the following line
--LIMIT 10
;

Could you explain your question a little more so the rest of us can understand what you are trying to accomplish here?

If you want the top 2 of any and all devices then use this:

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(sum(duration) AS CHAR), ')') 
FROM device_table
GROUP BY device_id
       , app_id
ORDER BY duration DESC
LIMIT 2 -- change this to how many you want
;
Related Question