获得缺席和存在 [英] Get No of absents and presence

问题描述

大家好......

i希望得到各个员工的总和（缺席）和总和（在场），下表结构澄清了我的问题...

 --------------------------------- --------------- 
 日期 | Emp_id | Absents 或 Presence 
 ----------------------------- ------------------- 
 1-1-2012 |  1  | P 
 2-1-2012 |  1  | P 
 3-1-2012 |  1  | A 
 4-1-2012 |  1  | P 
 
 1-1-2012 |  2  | P 
 2-1-2012 |  2  | A 
 3-1-2012 |  2  | A 
 4-1-2012 |  2  | P 
 
 1-1-2012 |  3  | P 
 2-1-2012 |  3  | P 
 3-1-2012 |  3  | P 
 4-1-2012 |  3  | P 
 ________________________________________________ 

1）在此表中有一个人员出勤表

2）P =存在。 A =缺席



我希望所有员工的存在和缺席次数如下所示

-------------------------------------------- -
Emp_id |否。 .presence | .absents
--------------------------- ------------------
1 | 3 （总和 P）| 1 （Sum A）

2 | 2 | 2

3 | 4 | 0
------------------------------- --------------

我不知道是否有可能......

请分享您的想法...

解决方案

尝试：

< pre lang =SQL> SELECT Emp_id，
SUM（ CASE WHEN AorP = ' P' 那么 1 ELSE 0 END ），
SUM（ CASE WHEN AorP = ' A' 那么 1 ELSE 0 END ）
FROM MyTable
GROUP BY Emp_Id

Hi all...
i want get sum(absents) and sum(presence) for the individual employees,the following table structure is clarify my question...

------------------------------------------------
Date     |   Emp_id    |   Absents OR Presence
------------------------------------------------
1-1-2012 |    1        |   P
2-1-2012 |    1        |   P
3-1-2012 |    1        |   A
4-1-2012 |    1        |   P

1-1-2012 |    2        |   P
2-1-2012 |    2        |   A
3-1-2012 |    2        |   A
4-1-2012 |    2        |   P

1-1-2012 |    3        |   P
2-1-2012 |    3        |   P
3-1-2012 |    3        |   P
4-1-2012 |    3        |   P
________________________________________________

1)In this table has a emp attendance table
2)P=presence. A=absents

I want the number of presence and absents the all employees like below

---------------------------------------------
Emp_id    | No.of.presence  | No.of.absents
---------------------------------------------
 1        |  3(sum of P)    |  1(Sum of A)

 2        |  2              |  2

 3        |  4              |  0
---------------------------------------------

I don't know it's possible or not...
please share your ideas...

解决方案

Try:

SELECT Emp_id, 
       SUM(CASE WHEN AorP='P' THEN 1 ELSE 0 END), 
       SUM(CASE WHEN AorP='A' THEN 1 ELSE 0 END) 
FROM MyTable 
GROUP BY Emp_Id

这篇关于获得缺席和存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！