CODEWITHSUNDEEP
mysqldatabasepostgresql
oyan11
oyan11

Reputation: 373

Join two select statement from one table

i have a table named attendance with 2 attributes (id, remarks). i want to display the tally of absence or late per id 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 Absent) or (ID and Late) using this code:

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

i can't display absent and late column simultaneously.. please help. thanks.

Upvotes: 4

Views: 157

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270677

Use a SUM(CASE) construct to separate the Absent and Late. For each one, the CASE returns a 1 or 0 if the value is matched, and then those are added up via the aggregate SUM() giving the total number. The concept at work 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

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146541

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;

Upvotes: 0

Taryn
Taryn

Reputation: 247810

This is basically a PIVOT. If you do not have access to a PIVOT function then, you can replicate it with an aggregate function and a 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

Upvotes: 2

Related Questions