Query Help....

[Arijit Chatterjee]

Dear All,
I need a help in writing a query.I am explaining the details.

Tables1 containing

EmployeeNo Status
========== ======
00001 A
00001 P
00001 L
00001 A
00001 P
00001 L
00002 A
00002 P
00002 L

Here A="Absent" ,P="Present" and L="Late"

I need the output without join like

==========================================
EmpNo | No Of Present | No Of Absent | No of Late
==========================================
Please help me out
Regards
Arijit Chatterjee

[David Portas]

SELECT employeeno,
COUNT(CASE status WHEN 'P' THEN 1 END) AS no_present,
COUNT(CASE status WHEN 'A' THEN 1 END) AS no_absent,
COUNT(CASE status WHEN 'L' THEN 1 END) AS no_late,
FROM Sometable
GROUP BY employeeno

BTW please post DDL with questions like this so that we don't have to guess
at datatypes, keys and constraints. Your table as posted doesn't have a
primary key which makes it difficult to be sure of your requirements or
whether any possible solution is correct or not.

--
David Portas
------------
Please reply only to the newsgroup
--