如何完整获取选中列并单独生成SUM列？SQL查询技术咨询

Hey there! I see exactly what's going on here—let's get this sorted out for you.

Why You're Only Getting One Row #

When you use an aggregate function like SUM() without a GROUP BY clause, SQL treats your entire result set as a single group. That's why it collapses everything into one row showing just the total sum. We need a way to calculate the sum without grouping all your rows together.

Solution 1: Use a Window Function (Recommended) #

Window functions let you compute aggregated values alongside your regular row data, no grouping required. Here's how to modify your query to keep all your desired columns and add a total sum column:

SELECT 
    f_name,
    l_name,
    teachers.first_name,
    teachers.t_id,
    p_id,
    paid_amount,
    family_id,
    date,
    SUM(payments.paid_amount) OVER () AS total_paid_amount -- Adds the overall sum to every row
FROM payments
LEFT JOIN family ON family.id = payments.family_id
LEFT JOIN teachers ON family.teacher_id = teachers.t_id;

If you ever need to calculate sums per a specific group (like per teacher or per family), you can add a PARTITION BY clause to the window function. For example, to get the sum per family:

SUM(payments.paid_amount) OVER (PARTITION BY family_id) AS family_total_paid

Solution 2: Subquery for Databases Without Window Function Support #

If you're working with an older database that doesn't support window functions (e.g., MySQL pre-8.0), you can use a subquery to fetch the total sum and cross-join it with your main results:

SELECT 
    f_name,
    l_name,
    teachers.first_name,
    teachers.t_id,
    p_id,
    paid_amount,
    family_id,
    date,
    total_sum.total_paid_amount
FROM payments
LEFT JOIN family ON family.id = payments.family_id
LEFT JOIN teachers ON family.teacher_id = teachers.t_id
CROSS JOIN (
    SELECT SUM(paid_amount) AS total_paid_amount FROM payments
) AS total_sum;

Full Version of Your Basic Query #

You mentioned needing the complete basic query (without the SUM). Here's the full, corrected version:

SELECT 
    f_name,
    l_name,
    teachers.first_name,
    teachers.t_id,
    p_id,
    paid_amount,
    family_id,
    date
FROM payments
LEFT JOIN family ON family.id = payments.family_id
LEFT JOIN teachers ON family.teacher_id = teachers.t_id;

Let me know if you need further tweaks—happy to help!

内容的提问来源于stack exchange，提问作者Captaîn Aamusane