Show a detail row AND its group total/percentage in one query — aggregate window functions vs GROUP BY

Question

Accepted Answer

103
   Show a detail row AND its group total / percentage in one query
  Level: Hard — The "detail + summary together" problem; window aggregates solve it elegantly where GROUP BY can't.

  
     Scenario: A report must show each employee's salary, alongside their department's total salary and what % of the department budget they consume — all on the same row. With GROUP BY, you lose the individual rows. With a subquery, you scan the table twice. What's the clean solution?
  

   Sample Data — employees
  
    
      
        namedept_idsalary
      
      
        Rumman1090000
        Krushna1060000
        Swetha1050000
        Ritesh2080000
        Manjula2040000
      
    
  

  The key insight: An aggregate window function (like SUM() OVER (PARTITION BY ...)) computes a group total without collapsing the rows. Unlike GROUP BY, which returns one row per group, the window version keeps every detail row and attaches the group's aggregate to each.

  
     The distinction: GROUP BY SUM() → one row per department (detail lost). SUM() OVER (PARTITION BY dept_id) → every employee row kept, each showing its department's total. Same aggregate, different granularity of output.
  

   Solution — Detail + Group Total + % of Total
SELECT
    name,
    dept_id,
    salary,
    SUM(salary) OVER (PARTITION BY dept_id)  AS dept_total,
    ROUND(
        salary / SUM(salary) OVER (PARTITION BY dept_id) * 100, 1
    )                                        AS pct_of_dept,
    AVG(salary) OVER (PARTITION BY dept_id)  AS dept_avg,
    COUNT(*)    OVER (PARTITION BY dept_id)  AS dept_headcount
FROM employees
ORDER BY dept_id, salary DESC;

  
    
      
        namedept_idsalarydept_totalpct_of_deptdept_avgheadcount
      
      
        Rumman109000020000045.0666673
        Krushna106000020000030.0666673
        Swetha105000020000025.0666673
        Ritesh208000012000066.7600002
        Manjula204000012000033.3600002
      
    
  
  Every employee row is preserved, each carrying its department's totals and the individual's share.

   Bonus — % of the GRAND Total (empty OVER clause)
SELECT
    name, salary,
    SUM(salary) OVER ()                       AS grand_total,   -- whole table
    ROUND(salary / SUM(salary) OVER () * 100, 1) AS pct_of_all
FROM employees;
-- An empty OVER () treats the ENTIRE result set as one window

   Window Aggregate vs GROUP BY vs Subquery
  
    
      
        ApproachKeeps detail rows?Table scans
      
      
        Window aggregateYesOne pass
        GROUP BYNo (collapses)One pass
        Correlated subqueryYesRepeated (slow)
      
    
  

  
     Watch the ORDER BY inside OVER: Adding ORDER BY inside the window turns a plain aggregate into a running total (with the default frame). For a flat group total, use PARTITION BY without an ORDER BY inside the OVER clause.
  

  
     Interviewer follow-up: "Show each employee's rank within their department AND the department total in the same query." → Combine both: RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) and SUM(salary) OVER (PARTITION BY dept_id) — you can use multiple window functions with different (or shared) OVER clauses in one SELECT.

Show a detail row AND its group total/percentage in one query — aggregate window functions vs GROUP BY

Answer with Explanation

Show a detail row AND its group total / percentage in one query

Sample Data — `employees`

Solution — Detail + Group Total + % of Total

Bonus — % of the GRAND Total (empty OVER clause)

Window Aggregate vs GROUP BY vs Subquery

name	dept_id	salary
Rumman	10	90000
Krushna	10	60000
Swetha	10	50000
Ritesh	20	80000
Manjula	20	40000

name	dept_id	salary	dept_total	pct_of_dept	dept_avg	headcount
Rumman	10	90000	200000	45.0	66667	3
Krushna	10	60000	200000	30.0	66667	3
Swetha	10	50000	200000	25.0	66667	3
Ritesh	20	80000	120000	66.7	60000	2
Manjula	20	40000	120000	33.3	60000	2

Approach	Keeps detail rows?	Table scans
Window aggregate	Yes	One pass
GROUP BY	No (collapses)	One pass
Correlated subquery	Yes	Repeated (slow)