Search code examples
sqlsql-serverhue

How to filter by the same column twice in SQL?

Say I have a column of dates, categories, and values to be summed. I wish to group by the categories and sum over the values such that it satisfies certain condition over a certain set of dates, and another condition over another set of dates.

Something like:

 SELECT categories, SUM(values) 
 FROM table 
 group by categories
 having SUM(values) = 0 WHEN dates between w AND x 
 AND having SUM(values) > 10000 WHEN dates between y and z

Thank you!

Solution

  • I think you want conditional aggregation:

     select categories, sum(values) 
 from table 
 group by categories
 having sum(case when dates between w and x then values else 0 end) = 0 and
        sum(case when dates between y and z then values else 0 end) > 10000 ;