多Group by子句需求：需按class_code+course_code及仅course_code统计学生数

Hey there! Sounds like you need two distinct student count aggregations—one grouped by both class_code and course_code, and another grouped solely by course_code. Since you already have the first part working, adding the second is pretty straightforward. Let's cover the most common implementation scenarios below:

Scenario 1: SQL Implementation #

Assuming you're working with a relational database, here's how to extend your existing code to get both statistics:

1. Existing: Count by class_code + course_code #

This is the part you already have, included for reference:

SELECT 
    class_code,
    course_code,
    COUNT(DISTINCT student_id) AS student_count
FROM 
    your_student_table
GROUP BY 
    class_code, course_code
ORDER BY 
    class_code, course_code;

2. New: Count by course_code only #

Add this query to get the total number of students per course, regardless of their class:

SELECT 
    course_code,
    COUNT(DISTINCT student_id) AS total_students_per_course
FROM 
    your_student_table
GROUP BY 
    course_code
ORDER BY 
    course_code;

Bonus: Combine both results in one output #

If you want to see both statistics in a single result set (with a label to distinguish them), use UNION ALL:

-- Grouped by class + course
SELECT 
    'class_course_combined' AS statistic_type,
    class_code,
    course_code,
    COUNT(DISTINCT student_id) AS student_count
FROM 
    your_student_table
GROUP BY 
    class_code, course_code

UNION ALL

-- Grouped by course only
SELECT 
    'course_only' AS statistic_type,
    NULL AS class_code, -- Fill with NULL since we don't need this column here
    course_code,
    COUNT(DISTINCT student_id) AS student_count
FROM 
    your_student_table
GROUP BY 
    course_code
ORDER BY 
    statistic_type, course_code, class_code;

Scenario 2: Python Pandas Implementation #

If you're processing data with Pandas, here's how to add the second aggregation to your existing code:

1. Existing: Count by class_code + course_code #

import pandas as pd

# Assume your dataset is stored in a DataFrame called df
class_course_stats = df.groupby(['class_code', 'course_code'])['student_id'].nunique().reset_index(name='student_count')
print("Student count by class + course:")
print(class_course_stats)

2. New: Count by course_code only #

Add this block to calculate course-level totals:

course_only_stats = df.groupby('course_code')['student_id'].nunique().reset_index(name='total_students_per_course')
print("\nTotal student count per course:")
print(course_only_stats)

Bonus: Combine results into one DataFrame #

To view both statistics side-by-side (with a type label):

# Add a type identifier to each result set
class_course_stats['statistic_type'] = 'class_course_combined'
course_only_stats['statistic_type'] = 'course_only'
course_only_stats['class_code'] = pd.NA  # Fill missing class code values

# Combine and reorder columns
combined_stats = pd.concat([class_course_stats, course_only_stats], ignore_index=True)
combined_stats = combined_stats[['statistic_type', 'class_code', 'course_code', 'student_count']]

print("\nCombined statistics:")
print(combined_stats)

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