DB query: How to count maximum for several columns

sql

Solution

Use derived table to get counts, and then select max:

select max(nbr_of_adults) max_adults,
       max(nbr_of_children) max_children
from
(
  select 
      sum(case when person_type = 'adult' then 1 else 0 end) as "nbr_of_adults",
      sum(case when person_type = 'child' then 1 else 0 end) as "nbr_of_children"
    from my_table
  group by claim_date
) a

Problem

Assume I have the following table ``` claim_date person_type ------------------------ 01-01-2012 adult 05-05-2012 adult 12-12-2012 adult 12-12-2012 adult 05-05-2012 child 05-05-2012 child 12-12-2012 child ``` When I execute the following query: ``` select claim_date, sum(case when person_type = 'adult' then 1 else 0 end) as "nbr_of_adults", sum(case when person_type = 'child' then 1 else 0 end) as "nbr_of_children" from my_table group by claim_date ; ``` I get this result here: ``` claim_date nbr_of_adults nbr_of_children --------------------------------------------- 01-01-2012 1 0 05-05-2012 1 2 12-12-2012 2 1 ``` What I would like to receive is the maximum number of adults (here: 2) and the maximum number of children (here: 2). Is there a way to achieve this with a single query? Thanks for any hints.

Original source