我需要使用 MySQL 根据列值进行聚合的方法 [英] I need the way to aggregate based on column value using MySQL
问题描述
我正在 Laravel 中学习图表,我需要为那些迟到或准时来的学生绘制每日学生出勤的折线图.我尝试编写 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
我尝试在同一个表上进行子查询以获取日常学生的数据,我还需要 7 个日期,但不是完整日期,例如日期存储在 db 中为 09/08/2019,但我需要将其作为日期为 08.
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
但是我得到了
[Err] 1241 - 操作数应包含 1 列
[Err] 1241 - Operand should contain 1 column(s)
,因为我不能在子查询中再次获取日期,而在 where 子句之后使用它.任何人请帮助我.
, because i can't use to fetch Date again in subquery while use it after where clause. Any one help me plz.
推荐答案
这里是一种基于列值聚合的方法.
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`;
这篇关于我需要使用 MySQL 根据列值进行聚合的方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!