0

My MySQL table is

I want to count different leave types from leaveType column, where team is HR.

My current code is:

SELECT COUNT(leaveType = 'Priviledge') as Priviledge, 
    COUNT(leaveType = 'Causal') as Causal 
FROM emp_leave_tracker WHERE team='HR' GROUP BY leaveType;
Dimitar
  • 4,402
  • 4
  • 31
  • 47
Alok Routray
  • 3
  • 5
  • What you want to do is a pivoting of a count+group of leaveType. You can find useful answers here https://stackoverflow.com/questions/7674786/mysql-pivot-table and here https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns – Aris2World Aug 04 '17 at 05:54
  • and what's your problem .. you have error ?? wrong result .. update your questioon add a proper data sample .. and the expected result – ScaisEdge Aug 04 '17 at 06:05
  • Why not a simple select leaveType, count(*) from emp_leave_tracker WHERE team='HR' GROUP BY leaveType; – wargre Aug 04 '17 at 06:06
  • Is 'privileDge' even a word? – Strawberry Aug 04 '17 at 06:12
  • @Strawberry sorry. Its privilege. typing error. – Alok Routray Aug 04 '17 at 09:32

1 Answers1

1

You might try this:

SELECT 
    SUM(IF(`leaveType` = 'Priviledge',1,0)) as `Priviledge`,
    SUM(IF(`leaveType` = 'Causal',1,0)) as `Causal`
FROM emp_leave_tracker 
WHERE team='HR';

The IF statement inside the sum returns a 1 when the leave type matches it's value, and 0 if not. The SUM function then adds up all the ones.

Sloan Thrasher
  • 4,953
  • 3
  • 22
  • 40