Employee Employee ID & amp; How many days are absent & amp; Half day for the given date range
Date of date of employee AID mpid status 1 10 Current 17-03-2015 2 10 absent 18-03-2015 3 10 Hafde 19-03- 2015 4 10 Current 20-03-2015 5 11 Current 21-03-2015 6 11 Absent 22-03-2015 7 11 Half day 23-03-2015
Expected production will be: < / P>
EmpID current absent HalfDay 10 2 1 1 11 1 1 1
Can you help me with SQL query?
Here I have tried
SELECT EMP.EMPID, (in case when EMP.STATUS = 'current', then COUNT (position) ELSE 0 END) Pres, (Case when EMP.STATUS = 'absent' then COUNT (position) 0 ELSE), (case when emp.status = 'HalfDay' then calculate (position) ELSE 0 END) EMPLOYEE EMP Group Emp.empid
COUNT function () If the value is not zero then it is always such that both of the CASE statement The sides will increase in:
COUNT (case status when 'current' then 1 ELSE 0) Current
So we need to use the SMS () ...
select empid, sum (case when status = 'current' then 1 and 0 end) present_tot, amount (case when condition = 'absent' then absent_tot, amount (case when case = 'HalfDay' then 1 and 0 end) half day _tot empid by empid order
... or use zero (NULL other segment) with both COUNT () outputs the same output, perhaps it is clear:
SQL & Gt; FIPD, count 2 (case status when presenting the 'presentant' then 1 end) present_tot, 3 count (case when position = 'absent' then 1 end) absent_tot, count 4 (case when position = 'HalfDay' then 1 end) halfday_tot 5 Employees 6 Group Empid 7 Order Empid 8 / EMPID PRESENT_TOT by ABSENT_TOT HALFDAY_TOT ---------- ---------------------------------- - - ------- 10 2 1 1 11 1 1 1 SQL & gt;
Note that we must use the order to guarantee the sequence of results set. Oracle introduced hashing optimization for aggregation in 10G, which means GROUP BY seldom gives a candidate sort order.
No comments:
Post a Comment