0

I don't know if my question is valid or not but I'm working on an attendance system, and I want to get some output for my project.

I have an attendance table that holds three columns: emp_id, atten_type and date. The atten_type column can contain one of three values; that is 'present', 'absent' and 'leave'.

Now, I want the output like this:

i got only one value like count(atten_type) from table where atten_type = "present"

Improve this question
2
  • its easier to be explicit like below rather than creating a table based on your contents. Pivot tables are very horrible and if you know the atten_type values up front use them.
    – danblack
    Commented Dec 3, 2018 at 5:07
  • Please provide source data which must produce the output you show...
    – Akina
    Commented Dec 3, 2018 at 5:25

1 Answer 1

Reset to default
1

So grouping by emp_id, month and suming the count based on atten_type?

SELECT emp_id,
    SUM(IF(atten_type='present',1,0) as 'atten_type as present',
    SUM(IF(atten_type='leave',1,0) as 'atten_type as leave',
    SUM(IF(atten_type='absent',1,0) as 'atten_type as absent',
    MONTH(`date`) as 'month'
FROM
    attendance
GROUP BY emp_id, `month`
Improve this answer
1
  • 1
    Author claims that DBMS is MySQL. So you do not need in IF() and can use simple SUM(atten_type='present')
    – Akina
    Commented Dec 3, 2018 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.