Friday, 30 October 2026

Write a query to perform a conditional aggregation (count males and females in each department).

To perform conditional aggregation in SQL, you combine the SUM() or COUNT() aggregate functions with a CASE WHEN statement. This allows you to inspect row attributes individually and increment separate totals within the same GROUP BY execution.
The Query
Assuming your table is named employees and contains columns for department and gender, here is the standard, optimized SQL query:
sql
SELECT 
    department,
    -- Method 1: SUM with CASE WHEN (Standard across all SQL flavors)
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count,
    
    -- Method 2: COUNT with CASE WHEN (Alternative syntax)
    COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS alt_male_count,
    COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS alt_female_count
FROM employees
GROUP BY department
ORDER BY department;
Use code with caution.
How It Works
  • The CASE Evaluator: For every row processed within a department, the CASE statement inspects the value of the gender column.
  • SUM(CASE ... THEN 1 ELSE 0): If the condition matches, it outputs a 1. If it doesn't match, it outputs a 0. The SUM() function then tallies up these numbers, effectively calculation a count.
  • COUNT(CASE ... THEN 1 END): The COUNT() function ignores NULL values. If the condition matches, it returns a 1. If it doesn't match, it implicitly returns NULL (since there is no ELSE clause), so it isn't counted.

Complete Playground Script (Schema + Test Data)
You can use this complete script to build the schema, load data, and instantly verify the execution logic:
sql
-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    gender VARCHAR(10)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, gender) VALUES
(1, 'Alice', 'Engineering', 'Female'),
(2, 'Bob', 'Sales', 'Male'),
(3, 'Charlie', 'Engineering', 'Male'),
(4, 'Diana', 'Engineering', 'Female'),
(5, 'Evan', 'Sales', 'Male'),
(6, 'Fiona', 'Marketing', 'Female'),
(7, 'George', 'Sales', 'Male'),
(8, 'Hannah', 'Marketing', 'Female');

-- 3. EXECUTE CONDITIONAL AGGREGATION
SELECT 
    department,
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department
ORDER BY department;
Use code with caution.
Expected Query Output
departmentmale_countfemale_count
Engineering12
Marketing02
Sales30

No comments:

Post a Comment

Note: only a member of this blog may post a comment.