mihagazvoda
mihagazvoda

Reputation: 1367

SQL query: group by, calculate difference between max and min in the group and group by again

My DB contains userID in the first column and the timestamp of some action (login or clicking the button). The first record (userID, time) is always user logging in, sometimes there is also second which means user also clicked the button - but that's not always the case. There are never more than 2 rows with the same userID.

"userID" "time"
"0" "1526474096000"
"1" "1526474097000"
"2" "1526474098000"
"3" "1526474099000"
"4" "1526474100000"
"1" "1526474106000"
"0" "1526474107000"

For example, here only users with ID 0 and 1 clicked the button.
I want to calculate histogram (or some other approximation) of time differences because there are too many users to for my RAM to import time difference for every user.
My SQL query looks like this (n is some histogram bin size):

SELECT COUNT(*), (MAX(time)-MIN(time)/n) as time_difference 
FROM table_name  
GROUP BY userID, time_difference

It doesn't work because "aggregate functions are not allowed in the GROUP BY clause".

Upvotes: 0

Views: 6975

Answers (2)

Error_2646
Error_2646

Reputation: 3781

Just use a subquery. I'm not totally sure what you are trying to do with that '/n', but this at least resolves your syntax error.

SELECT time_Difference,
       count(*)
  FROM (SELECT (MAX(time)-MIN(time)/n) as time_difference 
          FROM table_name  
         GROUP 
            BY userID
       ) TMP
 GROUP
    BY time_difference;

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

Are you trying to do this?

SELECT userID, (MAX(time)-MIN(time)/n) as time_difference 
FROM table_name  
GROUP BY userID 

Upvotes: 1

Related Questions