如何获取平均出勤人数？开发子查询从两表获取含完整详情的平均出勤

Hey there! I'll walk you through solutions for both of your questions clearly, with practical SQL examples tailored to common use cases.

If you're working with a single table that tracks attendance data (like daily counts per event or date), the core tool here is the AVG() aggregate function.

Let's assume you have a table named attendance_tracker with these fields:

• record_date (DATE): The date of the attendance record
• present_count (INT): Number of people present that day

To calculate the overall average attendance across all records, use this query:

SELECT AVG(present_count) AS average_daily_attendance
FROM attendance_tracker;

If you want to calculate averages grouped by a specific dimension (like per event or per month), add a GROUP BY clause to get segmented results:

SELECT 
    event_name,
    AVG(present_count) AS average_attendance_per_event
FROM attendance_tracker
GROUP BY event_name;

Let's say you have two related tables (a common setup for event and attendance data):

1. event_details: Stores full information about each event

   • event_id (INT, primary key)
   • event_name (VARCHAR): Name of the event
   • event_location (VARCHAR): Where the event was held
   • start_date (DATE): Event start date

2. attendance_logs: Tracks daily attendance counts for each event

   • log_id (INT, primary key)
   • event_id (INT, foreign key linking to event_details.event_id)
   • daily_present (INT): Number of attendees that day

To get each event's full details alongside its average daily attendance, you can use a correlated subquery. This subquery runs once for each row in the outer query, calculating the average specifically for that event:

SELECT 
    ed.event_id,
    ed.event_name,
    ed.event_location,
    ed.start_date,
    -- Correlated subquery to fetch average attendance for the current event
    (SELECT AVG(daily_present)
     FROM attendance_logs al
     WHERE al.event_id = ed.event_id) AS average_daily_attendance
FROM event_details ed;

How this works: #

• The outer query pulls all full details from event_details for every event.
• For each event row, the subquery filters attendance_logs to only records matching that event's event_id, then computes the average of daily_present for those records.
• The result is a single row per event, with all its contextual details plus the calculated average attendance.

If you want to exclude events that have no attendance records at all, add a WHERE EXISTS clause to filter those out:

SELECT 
    ed.event_id,
    ed.event_name,
    ed.event_location,
    ed.start_date,
    (SELECT AVG(daily_present)
     FROM attendance_logs al
     WHERE al.event_id = ed.event_id) AS average_daily_attendance
FROM event_details ed
WHERE EXISTS (
    SELECT 1 
    FROM attendance_logs al 
    WHERE al.event_id = ed.event_id
);

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