Alex Alex - 2 months ago 13
SQL Question

Counting distinct values output from a grouped SQL Count function

I've got a database that holds information about volunteers and their participation in a range of events.

The following query gives me a list of their names and total attendances

SELECT
volunteers.last_name,
volunteers.first_name,
count (bookings.id)
FROM
volunteers,
bookings
WHERE
volunteers.id = bookings.volunteer_id
GROUP BY
volunteers.last_name,
volunteers.first_name


I want the result table to show the distinct number of attendances and how many there are of each; So if five people did one event it'd display 1 in the first column and 5 in the second and so on.

Thanks

Answer

If I understand correctly, you want what I call a "histogram of histograms" query:

select numvolunteers, count(*) as numevents, min(eventid), max(eventid)
from (select b.eventid, count(*) as numvolunteers
      from bookings b
      group by b.eventid
     ) b
group by numvolunteers
order by numvolunteers;

The first column is the number of volunteers booked for an "event". The second is the number of events where this occurs. The last two columns are just examples of events that have the given number of volunteers.

Comments