CODEWITHSUNDEEP
sqloracle-database
Nikita Ribakovs
Nikita Ribakovs

Reputation: 315

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

Upvotes: 2

Views: 200

Answers (3)

Bostjan
Bostjan

Reputation: 361

Try this:

select
  sum(t.x_all) cnt_all,
  sum(t.x_staff) cnt_staff,
  sum(t.x_student) cnt_student
from (
  select
    1 x_all,
    case when v.volunteer_type = 'Staff' then 1 else 0 end x_staff,
    case when v.volunteer_type = 'Student' then 1 else 0 end x_student
  from volunteer v
) t

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 5

kareem
kareem

Reputation: 1

DECLARE @volunteer TABLE 
(Volunteer_id int 
,Volunteer_type varchar(50) )

INSERT INTO @volunteer  VALUES (1,'Staff')
INSERT INTO @volunteer  VALUES (1,'Staff')
INSERT INTO @volunteer  VALUES (1,'Staff')

INSERT INTO @volunteer  VALUES (1,'Student')
INSERT INTO @volunteer  VALUES (1,'Student')
INSERT INTO @volunteer  VALUES (1,'Student')


SELECT COUNT(Volunteer_id) AS "TOTAL VOLUNTEERS"
       ,(
           SELECT  COUNT(tb1.Volunteer_id) AS "TOTAL VOLUNTEERS"
           FROM   @volunteer AS tb1
           WHERE  tb1.Volunteer_id = tb.Volunteer_id
             AND  tb1.Volunteer_type = 'Staff'


       )
       ,(
           SELECT COUNT(volunteer_Student.Volunteer_id) AS "TOTAL VOLUNTEERS"
           FROM   @volunteer AS volunteer_Student
           WHERE  volunteer_Student.Volunteer_id = tb.Volunteer_id
             AND  volunteer_Student.Volunteer_type = 'Student'
       )
FROM   @volunteer tb
GROUP BY tb.Volunteer_id

Upvotes: 0

Related Questions