Sum rows together with the same ID but different event logs with conditions

A table has 3 different fields, the first being user_id, event_log, uploaded_files.

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?

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;

Demo on DB Fiddle

user_id | total_files
------: | ----------:
      1 |           3
      3 |           5

Advertisement

Answer