I need the way to aggregate based on column value using MySQL

I am learning Charts in Laravel, i need to draw Line graph for daily Student Attendance for those students come late or on time. I tried to write MYSQL query but it doesn’t work

I tried subquery on same table to get data for daily students and i also need 7 dates only not full date, like date is stored in db as 09/08/2019 but i need it as 08 as date.

SELECT Date, COUNT(*) AS TimeStudent
   FROM attendance WHERE `Attendance`='OnTime' AND (SELECT COUNT(*) AS 
   LateStudent FROM attendance 
   WHERE `Attendance`='Late'
   GROUP BY `Date`
   ORDER BY LateStudent DESC) 
   GROUP BY `Date`
   ORDER BY TimeStudent DESC

but i got

[Err] 1241 – Operand should contain 1 column(s)

, because i can’t use to fetch Date again in subquery while use it after where clause. Any one help me plz.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Here is a way to aggregate based on column value.

This query will give you count of on time and late student for a particular date.

SELECT 
    `Date`,
    DATE_FORMAT(`Date`, '%d') AS Month_Date,  -- You can modify it as per your requirement
    SUM(IF(`Attendance` = 'OnTime', 1, 0)) AS OnTime_Count,
    SUM(IF(`Attendance` = 'Late', 1, 0)) AS Late_Count
FROM attendance
WHERE `Date` >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY `Date`;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x