Q:

SQL query to display a "total column" across several rows

assume I have the following table
claim_id | person_id | age_category | amount
--------------------------------------------
       1 |         1 |        adult |  10.00
       1 |         2 |        adult |  10.00
       1 |         3 |     juvenile |   8.00
       1 |         4 |        child |   5.00
       2 |         5 |        adult |  15.00
       3 |         6 |        adult |  12.00
       3 |         7 |        child |   6.00
     ...
     100 |       250 |        child |   7.00

So several people could belong to the same claim.
What I already managed to achieve is such a result table:
category | total people |     amount
------------------------------------
adult    |          150 | 300'000.00
juvenile |           20 |  40'000.00
child    |           80 | 160'000.00

with the following query:
select 
    age_category as "category"
    count(*) as "total people",
    sum(amount) as "amount"
from
    my_table
group by
    age_category

Is there a way how I can count the number of claims and display it in the same result table? E.g. something like:
category | total claims | total people |     amount
---------------------------------------|-----------
adult    |          100 |          150 | 300'000.00
juvenile |              |           20 |  40'000.00
child    |              |           80 | 160'000.00

Thanks for any hints!
P.S.: I am using DB2

A:

try this:
select 
    age_category as "category",
    COUNT(distinct claim_id ) as "total_claims", --  <-- add this line 
    count(*) as "total people",
    sum(amount) as "amount"

from
    my_table
group by
    age_category

EDIT:
AS per your comment, use this query
select 
    age_category as "category",
    case when age_category='adult' then COUNT(distinct claim_id ) end 
                                                         as "total_claims" , 
    count(*) as "total people",
    sum(amount) as "amount"

from
    my_table
group by
    age_category  

SQL Fiddle Demo

