SQL查询报错Subquery Returns More Than One Row，求二月出勤率计算语句修复

Hey there! Let's figure out why you're getting that "subquery returns more than 1 row" error and fix your attendance percentage calculation for February.

First off, that error pops up when you use a subquery that spits out multiple rows in a spot where SQL expects just one value—like if you tried to plug a multi-row subquery directly into your SELECT clause without grouping or aggregating it properly. Chances are your original query had separate subqueries for counting "Hadir" entries and total absen entries, but those subqueries weren't filtered or grouped to return a single value per tutor (or per whatever grouping you need).

Let's build a cleaner, error-free query that avoids this issue entirely. I'll make reasonable assumptions about how your tables link together (adjust the JOIN conditions if your schema is different):

SELECT
    -- 保留这些字段如果需要按导师分组；如果只需要全局二月出勤率，去掉这些和GROUP BY
    t.tutor_id,
    t.tutor_name,
    -- 计算出勤率：统计"Hadir"数量 / 总签到数 * 100，保留两位小数
    ROUND(
        (COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) * 100.0) / COUNT(a.absen_id),
        2
    ) AS attendance_percentage
FROM tutor t
-- 关联桥接表absen_user，假设tutor主键是tutor_id，对应absen_user的user_id
JOIN absen_user au ON t.tutor_id = au.user_id
-- 关联absen表，假设absen_user主键是absen_user_id，对应absen的absen_user_id
JOIN absen a ON au.absen_user_id = a.absen_user_id
-- 如果aktivitas表用来限定活动范围就保留关联；不需要的话可以删掉这行
JOIN aktivitas ak ON a.aktivitas_id = ak.aktivitas_id
-- 精准过滤二月数据：用日期范围避免跨年度问题（示例为2024年二月）
WHERE a.absen_date >= '2024-02-01' 
  AND a.absen_date < '2024-03-01'
-- 按导师分组统计；全局统计就删掉这行
GROUP BY t.tutor_id, t.tutor_name;

Why this works: #

• Instead of relying on separate subqueries that might return multiple rows, we do all counting in one pass. COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) only counts entries where status is "Hadir" (NULL values from the CASE are ignored by COUNT).
• COUNT(a.absen_id) gives the total number of absen entries per group (or globally if no grouping is used).
• Using 100.0 instead of 100 ensures we get a decimal result instead of integer division (which would truncate the percentage).

If you really want to use subqueries: #

If you prefer sticking with subqueries, you need to ensure each one returns exactly one row per group. Here's how to adjust them:

SELECT
    t.tutor_id,
    t.tutor_name,
    ROUND(
        (SELECT COUNT(*) FROM absen a 
         JOIN absen_user au ON a.absen_user_id = au.absen_user_id
         WHERE au.user_id = t.tutor_id 
           AND a.status = 'Hadir'
           AND a.absen_date >= '2024-02-01' 
           AND a.absen_date < '2024-03-01') * 100.0 /
        (SELECT COUNT(*) FROM absen a 
         JOIN absen_user au ON a.absen_user_id = au.absen_user_id
         WHERE au.user_id = t.tutor_id
           AND a.absen_date >= '2024-02-01' 
           AND a.absen_date < '2024-03-01'),
        2
    ) AS attendance_percentage
FROM tutor t;

Note that this is less efficient than the first approach—it runs two subqueries per tutor, whereas the first method does all calculations in a single scan.

Pro tips to avoid issues: #

• Always use date ranges (>= and <) instead of MONTH(a.absen_date) = 2—the latter will pick up February entries from every year, which is rarely intended.
• Double-check your JOIN conditions to avoid accidental Cartesian products (which would inflate your counts and give wrong percentages).
• If you don't need per-tutor stats, remove the GROUP BY clause and tutor fields to get a single overall attendance percentage for February.

内容的提问来源于stack exchange，提问作者sami aji