Search code examples
sqlgroup-bysumconditional-statementshaving-clause

SQL - Conditional Group By Statement With Sum

I have a table with employees and my task is to calculate the overall annual earnings per branch.

The table looks like this:

Original Table

The special condition is to only sum the total earnings of branches were no voluntary employees are working.

The preferred results would look like this:

Results Table

Is there a lean possibility in SQL to sum only groups that fulfill such a special condition without using the help of joins and additional select statements?

Thanks a lot!

Solution

  • You can use aggregation, and filter out branches that have at least one voluntary with a having clause:

    select branch_id, sum(earnings) as earnings
from earnings_table
group by branch_id
having max(case when employee_type = 'Voluntary' then 1 else 0 end) = 0

    Some databases have neater options to express the having clause.

    In MySQL or SQLite:

    having max(employee_type = 'Voluntary') = 0

    In Postgres;

    having not bool_or(employee_type = 'Voluntary')