Calculate the total salary for IT and HR departments separately in one query.

Problem Statement

<p>Calculate the total salary for IT and HR <code>departments</code> separately in one query.</p>

Examples

Input: employees table: +----+---------+------------+--------+ | id | name | department | salary | +----+---------+------------+--------+ | 1 | Alice | IT | 90000 | | 2 | Bob | HR | 60000 | | 3 | Charlie | IT | 120000 | | 4 | Dana | HR | 75000 | +----+---------+------------+--------+

Output: +----------+----------+ | it_total | hr_total | +----------+----------+ | 210000 | 135000 | +----------+----------+

Explanation: The records are grouped by category and the aggregate calculation is applied to produce the summary result.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Use a conditional CASE WHEN ... THEN ... ELSE ... END expression to transform values or implement if-else logic. 💡 Hint 2: Each condition is evaluated in order; provide a sensible ELSE fallback if unhandled cases exist. 💡 Hint 3: Place the CASE expression in the SELECT list with an alias: SELECT CASE WHEN condition THEN val ELSE default END AS column_name FROM ...;

Editorial & Approach

Problem Overview & Intuition

To solve "CASE Inside Aggregate", we query the relational database engine using declarative SQL. The goal is to calculate the total salary for it and hr departments separately in one query. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply row projections to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT SUM(CASE WHEN department = 'IT' THEN salary ELSE 0 END) AS it_total, SUM(CASE WHEN department = 'HR' THEN salary ELSE 0 END) AS hr_total FROM employees;

Complexity Analysis

Time Complexity O(N)

Space Complexity O(1)

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

CASE Inside Aggregate

Medium

Calculate the total salary for IT and HR departments separately in one query.

Example Scenarios

1Example 1

Input:

employees table

id	name	department	salary
1	Alice	IT	90000
2	Bob	HR	60000
3	Charlie	IT	120000
4	Dana	HR	75000

Output:

it_total	hr_total
210000	135000

Explanation:

The records are grouped by category and the aggregate calculation is applied to produce the summary result.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.