CODEWITHSUNDEEP
sqloracle-database
Sudhir Mane
Sudhir Mane

Reputation: 306

SQL Query to fetch employee Attendence

I need to write query on employee table to fetch the employee with employee ID & how many days he is present absent & half-day for 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 will be :

EmpID   Present Absent  HalfDay
10       2      1        1
11       1      1        1

Can you please help me with the Sql query ?

Here Is the query I tried

SELECT EMP.EMPID,
(CASE WHEN EMP.STATUS = 'Present' THEN COUNT(STATUS) ELSE 0 END) Pres,
(CASE WHEN EMP.STATUS = 'Absent' THEN COUNT(STATUS) ELSE 0 END) ABSENT, 
(CASE WHEN emp.status = 'HalfDay' THEN Count(status) ELSE 0 END) HalfDay
FROM EMPLOYEE EMP GROUP BY emp.empid

Upvotes: 0

Views: 1280

Answers (2)

APC
APC

Reputation: 146299

The COUNT() function tests if the value is NOT NULL. Therefore it will always increment for both sides of a CASE statement like this:

COUNT(CASE Status WHEN 'Present' THEN 1 ELSE 0) AS Present

So we need to use SUM() ...

select empid,
    sum(case when status='Present' then 1 else 0 end) present_tot,
    sum(case when status='Absent' then 1 else 0 end)  absent_tot,
    sum(case when status='HalfDay' then 1 else 0 end) halfday_tot
from employee
group by empid
order by empid
/

... or use COUNT() with a NULL else clause. Both produce the same output, perhaps this one is clearer:

SQL> select empid,
  2      count(case when status='Present' then 1 end) present_tot,
  3      count(case when status='Absent' then 1 end)  absent_tot,
  4      count(case when status='HalfDay' then 1 end) halfday_tot
  5  from employee
  6  group by empid
  7  order by empid 
  8  /

     EMPID PRESENT_TOT ABSENT_TOT HALFDAY_TOT
---------- ----------- ---------- -----------
        10           2          1           1
        11           1          1           1

SQL>

Note that we need to use ORDER BY to guarantee the order of the result set. Oracle introduced a hashing optimization for aggregations in 10g which meant GROUP BY rarely returns a predictable sort order.

Upvotes: 4

Vinish Kapoor
Vinish Kapoor

Reputation: 679

Replace 0 with null because it would be also come in count and added the where clause for date range, check the example below:

select empID,
count(case when status='Present' then 1 else null end) Present_Days,
count(case when status='Absent' then 1 else null end)  Absent_Days,
count(case when status='HalfDay' then 1 else null end) HalfDays
from Employee 
where date >= to_date('17mar2015') and date <= to_date('23mar2015')
group by empID

Upvotes: 0

Related Questions