Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
397 views
in Technique[技术] by (71.8m points)

laravel - 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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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`;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...