CODEWITHSUNDEEP
mysqlsql
Muhammad Faizan Khan
Muhammad Faizan Khan

Reputation: 10551

SQL Query to bring group result of a column in multiple Column

Trying to make a query which generate this kind of result

EmpId,  EmpSickLeave,EmpCasualLeave, EmpAnnualLeave, TotalLeave
1,       1,           0,                0            1
2,       0,           0,                2            2
3,       0,           1,                0            1
4,       0,           1,                0            1  
5,       1,           0,                0            1

while I have two tables

Table EmpLeave:: include a column EmpId, Date, EmpLeaveTypeID

Table EmpLeaveType:: include EmpLeaveTypeID and LeaveName

DATA In tables

EmpLeaveType Table
EmpLeaveTypeID , LeaveName
1,              Sick Leave
2,              Annual
3,              Casual

EmpLeave Table
column EmpId, Date, EmpLeaveTypeID
            1, 2015-07-01, 1
            3, 2015-07-02, 2
            5, 2015-07-04, 1
            4, 2015-07-04, 2
            2, 2015-07-05, 2
            2, 2015-07-07, 2

I am pulling my hair and unable to brind this kind of result. Is this possible?

  EEmpId,  EmpSickLeave,EmpCasualLeave, EmpAnnualLeave, TotalLeave
1,       1,           0,                0            1
2,       0,           0,                2            2
3,       0,           1,                0            1
4,       0,           1,                0            1  
5,       1,           0,                0            1

This wrong thing I have tried!

    SELECT * count(EmpLeaveTypeID) FROM `EmpLeaveType`
WHERE SwitchDate Between '2015-07-01' AND '2015-07-28'
group by EmpLeaveType, EmpId

Upvotes: 1

Views: 47

Answers (4)

Jerome Anthony
Jerome Anthony

Reputation: 8021

I have omitted the date between, as it's a simple where addition.

Try the SQL query below:

select a.EmpId, count(Date), LeaveName from EmpLeave as a, mpLeaveType as b
where a.EmpLeaveTypeID = b.id
group by a.EmpId, a.EmpLeaveTypeID

Check the sqlfiddle

Upvotes: 0

Yathish Manjunath
Yathish Manjunath

Reputation: 2029

Please check the below : 

 SELECT 
 temp.EmpId , 
 temp.EmpSickLeave, 
 temp.EmpCasualLeave, 
 temp.EmpAnnualLeave,
 (temp.EmpSickLeave + temp.EmpCasualLeave + temp.EmpAnnualLeave ) 
 as  TotalLeave 
 FROM 
( SELECT 
 EmpId, 
 SUM( CASE WHEN EmpLeaveTypeID=1 THEN 1 ELSE 0 END ) as EmpSickLeave,
 SUM( CASE WHEN EmpLeaveTypeID=2 THEN 1 ELSE 0 END ) as EmpCasualLeave,
 SUM( CASE WHEN EmpLeaveTypeID=3 THEN 1 ELSE 0 END ) as EmpAnnualLeave
 FROM EmpLeave 
 WHERE Date Between '2015-07-01' AND '2015-07-28'
 group by EmpId ) temp

Upvotes: 0

lc.
lc.

Reputation: 116468

This looks to be a conditional count, where you want to count only certain rows. To achieve this, you can use a SUM with a CASE statement:

select EmpId as EEmpId,
    sum(case when EmpLeaveTypeID = 1 then 1 else 0 end) as EmpSickLeave,
    sum(case when EmpLeaveTypeID = 2 then 1 else 0 end) as EmpCasualLeave,
    sum(case when EmpLeaveTypeID = 3 then 1 else 0 end) as EmpAnnualLeave,
    count(*) as TotalLeave
from EmpLeave
where ...
group by EmpId

What this does is add either 0 or 1 for each row, based on whether the EmpLeaveTypeID matches. You can then use COUNT to get the total number of rows for the TotalLeave column, as I have shown above.

SQL Fiddle demo

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

Please try the SELECT query below. It does not take into consideration that leave types may be changed someday. SQL fiddle link http://sqlfiddle.com/#!9/0fd13/2 The query simply does an conditional aggregate to calculate leaves for each employee.

SELECT 
EmpId, 
SUM(CASE WHEN EmpLeaveTypeID=1 THEN 1 ELSE 0 END) as EmpSickLeave,
SUM(CASE WHEN EmpLeaveTypeID=2 THEN 1 ELSE 0 END) as EmpCasualLeave,
SUM(CASE WHEN EmpLeaveTypeID=3 THEN 1 ELSE 0 END) as EmpAnnualLeave,
COUNT(1) as TotalLeave
FROM `EmpLeave`
WHERE Date Between '2015-07-01' AND '2015-07-28'
group by EmpId

Upvotes: 1

Related Questions