A table has 3 different fields, the first being user_id
, event_log
, uploaded_files
.
x
user_id event_log uploaded_files
--------------------------------------------
1 0001 1
1 0002 1
1 0003 1
2 0004 5
2 0005 2
2 0006 1
3 0007 1
3 0008 4
I am aiming to merge the user_id rows together in one and sum up the uploaded_files column, while implementing conditions such as total files > 1 and <6
. Ideally I would like this:
user_id total_files
--------------------------
1 3
3 5
So far I have only been able to get to:
user_id total_files
--------------------------
1 3
2 8
3 5
I have gotten there by using:
SELECT user_id, sum(files_uploaded) total_files
FROM my_table
GROUP BY user_id
ORDER BY user_id;
I’ve tried to add conditions: ORDER BY user_id WHERE total_files >1 AND total_files <6
, but I am unable to. Is there something that I am missing?
Advertisement
Answer
You cannot use aggregate expressions in the WHERE
clause; for this, you need to use the HAVING
clause:
SELECT user_id, sum(files_uploaded) total_files
FROM my_table
GROUP BY user_id
HAVING SUM(files_uploaded) > 1 AND SUM(files_uploaded) < 6
ORDER BY user_id;
user_id | total_files ------: | ----------: 1 | 3 3 | 5