SQL SUM function with added

Question

total novice here with SQL SUM function question. So, SUM function itself works as I expected it to:

select ID, sum(amount) 
from table1
group by ID

There are several records for each ID and my goal is to summarize each ID on one row where the next column would give me the summarized amount of column AMOUNT.

This works fine, however I also need to filter out based on certain criteria in the summarized amount field. I.e. only look for results where the summarized amount is either bigger, smaller or between certain number.

This is the part I'm struggling with, as I can't seem to use column AMOUNT, as this messes up summarizing results.

Column name for summarized results is shown as "00002", however using this in the between or > / < clause does not work either. Tried this:

select ID, sum(amount)
from table1
where 00002 > 1000
group by ID

No error message, just blank result, however plenty of summarized results with values over 1000.

Unfortunately not sure on the engine the database runs on, however it should be some IBM-based product.

use alias as column names. And as mentioned use having to use aggregated column operations. select ID, sum(amount) as sum_amount from table1 group by ID having sum(amount) = 'some value' — Utsav
– Utsav, Commented May 2, 2017 at 10:11

Juan · Accepted Answer · 2017-05-02 10:17:08Z

1

The WHERE clause will filter individual rows that don't match the condition before aggregating them.

If you want to do post aggregation filtering you need to use the HAVING Clause.

HAVING will apply the filter to the results after being grouped.

select ID, sum(amount)
from table1
group by ID
having sum(amount) > 1000

answered May 2, 2017 at 10:17

Juan

3,70323 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL SUM function with added

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related