Reputation: 1367
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
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
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