Question 175130 in Mysql Question posted in
The official MySQL documentation can be found here.

Do you want to rewrite this? Do you want to rewrite this?

My dbfiddle

I have a table that stores user counts every 5 minutes for multiple devices. Each device has it’s own row per timestamp. As I show in my fiddle, I created a query to sum all of the rows per create_time for a particular day. I then added to the query to find the max sum. I then added to find the create_time of the group that has that max sum. I then combined them all together to get the individual rows that make up the group for the create_time that had the max sum. Great!

While it works for a specific day, I just can’t figure out how to do it for multiple days. I thought I could throw some group by date(create_time)’s in there but I clearly am not grasping group by with having. From this point, I can’t say I’ve tried anything else because I just can’t wrap my brain around it.

Any help would be much appreciated!

Tags:

2

Answers


  1. 0

    is this what you’re looking for? this code groups the users sums by date only

    select date(create_time) as date_create,
sum(users) as ut
from test
group by date_create;
    Login or Signup to reply.
  2. 0

    I don’t think that I completely understand your question. From what I understand you can run your query for each date in the database by using a procedure and cursors.

    CREATE PROCEDURE AggregateMaxPerDay()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE uniqueDate DATE;
  DECLARE cur CURSOR FOR SELECT DISTINCT DATE(create_time) FROM test;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur;
 read_loop: LOOP
FETCH cur INTO uniqueDate;
IF done THEN
  LEAVE read_loop;
END IF;
SET @query = CONCAT('SELECT DISTINCT id, create_time AS stat_time, users
                    FROM test
                    WHERE create_time = (
                      SELECT create_time FROM (
                        SELECT create_time, SUM(users) AS ut
                        FROM test
                        WHERE DATE(create_time) = "', uniqueDate, '"
                        GROUP BY create_time
                      ) t2
                      WHERE t2.ut = (
                        SELECT MAX(t.ut) AS ut
                        FROM (
                          SELECT create_time, SUM(users) AS ut
                          FROM test
                          WHERE DATE(create_time) = "', uniqueDate, '"
                          GROUP BY create_time
                        ) t
                      )
                    );');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
 CLOSE cur;
END;


CALL AggregateMaxPerDay();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search