Attach two select statements from the same table

Question

I have a table with the name of attendance with 2 attributes (id, comments). I want to display the absence account or be late for one identifier from the attendance table.

Attendance Table |ID | Remarks | ============================= |1 | Absent | |1 | Late | |2 | Absent | |2 | Absent | |3 | Late | Sample Output |ID | Absent | Late | ================================== |1 | 1 | 1 | |2 | 2 | | |3 | | 1 | 

currently I can only output 2 columns (ID and Missing) or (ID and Late) with this code:

 SELECT id, count(remarks) AS Absent FROM attendance WHERE remarks = 'Absent' GROUP BY id; 

I cannot display the missing and late column at the same time .. please help. thanks.

Answer 1

This is basically PIVOT . If you do not have access to the PIVOT function, then you can replicate it using the aggregate function and the CASE statement:

 select id, sum(case when remarks = 'Absent' then 1 else 0 end) Absent, sum(case when remarks = 'Late' then 1 else 0 end) Late from attendance group by id 

See SQL Fiddle with Demo

Or you can use COUNT() :

 select id, count(case when remarks = 'Absent' then 1 else null end) Absent, count(case when remarks = 'Late' then 1 else null end) Late from attendance group by id; 

See SQL Fiddle with Demo

Answer 2

Use the SUM(CASE) construct to separate Absent and Late . For each of them, CASE returns 1 or 0 if the value is matched and then added through the SUM() collection giving the total number. The concept used here is known as a pivot table .

 SELECT id, SUM(CASE WHEN Remarks = 'Absent' THEN 1 ELSE 0 END) AS Absent, SUM(CASE WHEN Remarks = 'Late' THEN 1 ELSE 0 END) AS Late FROM attendance GROUP BY id 

Answer 3

to try:

  SELECT id, Sum (Case remarks When 'Absent' Then 1 End) Absent, Sum (Case remarks When 'Late' Then 1 End) Late FROM attendance GROUP BY id;