oracle - SQL Query to fetch employee Attendence -

i need write query on employee table fetch employee employee id & how many days present absent & half-day given date range.

employee             aid empid   status     date 1   10      present   17-03-2015 2   10      absent    18-03-2015 3   10      halfday   19-03-2015 4   10      present   20-03-2015 5   11      present   21-03-2015 6   11      absent    22-03-2015 7   11      halfday   23-03-2015 

expected output :

empid   present absent  halfday 10       2      1        1 11       1      1        1 

can please me sql query ?

here query tried

select emp.empid, (case when emp.status = 'present' count(status) else 0 end) pres, (case when emp.status = 'absent' count(status) else 0 end) absent,  (case when emp.status = 'halfday' count(status) else 0 end) halfday employee emp group emp.empid 

the count() function tests if value not null. therefore increment both sides of case statement this:

count(case status when 'present' 1 else 0) present  

so need use sum() ...

select empid,     sum(case when status='present' 1 else 0 end) present_tot,     sum(case when status='absent' 1 else 0 end)  absent_tot,     sum(case when status='halfday' 1 else 0 end) halfday_tot employee group empid order empid / 

... or use count() null else clause. both produce same output, perhaps 1 clearer:

sql> select empid,   2      count(case when status='present' 1 end) present_tot,   3      count(case when status='absent' 1 end)  absent_tot,   4      count(case when status='halfday' 1 end) halfday_tot   5  employee   6  group empid   7  order empid    8  /       empid present_tot absent_tot halfday_tot ---------- ----------- ---------- -----------         10           2          1           1         11           1          1           1  sql> 

note need use order guarantee order of result set. oracle introduced hashing optimization aggregations in 10g meant group returns predictable sort order.