CODEWITHSUNDEEP
mysqlsql
Android_dev
Android_dev

Reputation: 117

how to write query to get the data based on month?

My Database table is::

attendence date     admission number         attendence

  2013-10-2         LSTM-0008/2013-2014      present

 2013-10-19         LSTM-0008/2013-2014      absent

  2013-9-20         LSTM-0008/2013-2014      present

above one is my database table.

i want to display table like this based on database table:

 MonthName     totalWorkingDays     Present     absent

 october            26                 1          1
 november           26                 1          0

i wrote mysql query like this: 

SELECT DISTINCT monthname(attendencedate)as monthname , COUNT (*) as totalworking days, 
  (SELECT COUNT(*) FROM lstms_attendence WHERE attendence='present' AND addmissionno='LSTM-0008/2013-2014') as present,
  (SELECT COUNT(*) FROM lstms_attendence WHERE attendence='absent' AND addmissionno='LSTM-0008/2013-2014') as absent 
FROM lstms_attendence 
WHERE addmissionno='LSTM-0008/2013-2014'
GROUP BY attendencedate;

its not working for me any one give me suggestions.

Upvotes: 2

Views: 670

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

Try this:

SELECT monthname(attendencedate) AS monthname,
  COUNT(*) AS totalworking_days,
  SUM(CASE WHEN attendence = 'present' THEN 1 ELSE 0 END) AS present,
  SUM(CASE WHEN attendence = 'absent' THEN 1 ELSE 0 END AS absent      
FROM lstms_attendence
WHERE addmissionno = 'LSTM-0008/2013-2014'
GROUP BY monthname(attendencedate);

It will SUM 1 for every row that has attendence = 'present' in the present column and 0 otherwise. The same for attendence = 'absent'

Upvotes: 2

Related Questions