Nikita Ribakovs Nikita Ribakovs - 6 days ago 5
SQL Question

COUNT values in different columns of one table SQL

For example, my table has data - 10 volunteers.
There are two types of volunteers - students and staff.
How can insert few columns in one table with this view:

COUNT(Volunteer_id), COUNT(Volunteer_id) WHERE Volunteer_type = 'Student',
COUNT(Volunteer_id WHERE Volunteer_type = 'Staff'

SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
from volunteer
UNION
SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
from volunteer
WHERE Volunteer_type = 'Staff'
UNION
SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
from volunteer
WHERE Volunteer_type = 'Student'


This statements are represented as rows for now, but I want to make them columns

Answer

Use conditional aggregation:

SELECT
    COUNT(*) AS "Total Volunteers",
    COUNT(CASE WHEN Volunteer_tpye = 'Staff' THEN 1 END) AS "Staff Volunteers",
    COUNT(CASE WHEN Volunteer_tpye = 'Student' THEN 1 END) AS Student
FROM volunteers
Comments