SQL连接TEILNEHMERKURS与KURS表并统计数据的语句求助

Hey Stefan, let's work through this SQL problem together! Since you’ve got the TEILNEHMERKURS (participant-course junction table, I assume) and KURS (course table) to work with, here’s a structured approach to build the right query for your statistical needs:

First up, let’s make sure we’re joining the tables correctly. Typically, KURS will have a primary key like kurs_id, and TEILNEHMERKURS will have a matching foreign key (also likely kurs_id) to link participants to their courses. If your schema uses different column names, adjust the join condition accordingly.

Below are examples of common stats you might need—adapt these to your specific output requirements:

Example 1: Count Participants per Course #

If you want to see how many participants are enrolled in each course (including courses with zero participants):

SELECT 
    k.kurs_name, -- Replace with your actual course name column
    COUNT(t.teilnehmer_id) AS total_participants
FROM KURS k
LEFT JOIN TEILNEHMERKURS t ON k.kurs_id = t.kurs_id
GROUP BY k.kurs_id, k.kurs_name
ORDER BY total_participants DESC;

Using LEFT JOIN ensures courses with no enrolled participants still appear in the results with a count of 0.

Example 2: Stats by Course Category #

If KURS has a category column (like kurs_kategorie), here’s how to calculate average participants per category:

SELECT 
    course_stats.kurs_kategorie,
    AVG(course_stats.participant_count) AS avg_participants_per_category
FROM (
    SELECT 
        k.kurs_id,
        k.kurs_kategorie,
        COUNT(t.teilnehmer_id) AS participant_count
    FROM KURS k
    LEFT JOIN TEILNEHMERKURS t ON k.kurs_id = t.kurs_id
    GROUP BY k.kurs_id, k.kurs_kategorie
) AS course_stats
GROUP BY course_stats.kurs_kategorie;

Since your current statement isn’t giving the expected output, could you share a few more details?

• The full schema of both tables (column names and data types)
• Your existing SQL query
• A sample of what you expected vs. what you’re actually getting

With that info, I can pinpoint exactly where the issue is and tweak the query to match your needs perfectly.

内容的提问来源于stack exchange，提问作者Stefan Leith