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"

  • 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 Dec 3 '18 at 5:07
  • Please provide source data which must produce the output you show... – Akina Dec 3 '18 at 5:25

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'
GROUP BY emp_id, `month`
  • 1
    Author claims that DBMS is MySQL. So you do not need in IF() and can use simple SUM(atten_type='present') – Akina Dec 3 '18 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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