CODEWITHSUNDEEP
sqlsql-servert-sql
Allen
Allen

Reputation: 712

Adding Max to a Result After Group By

I'm running the following SQL Server query which gives me what I want below.

SELECT company,
       benefit,
       avg(perVal) AS P,
       VAR(perVal) AS V
FROM Percept_Responses
GROUP BY company,
         benefit
company benefit P V
Competitor 1 Benefit 1 5.5 0.5
Competitor 3 Benefit 1 7.5 8
Competitor 1 Benefit 2 4.5 0.5
Competitor 3 Benefit 2 6 2

But now I would like to add the maximum P value for each benefit... so an extra row for each benefit with the maximum P value.

company benefit P V
Competitor 1 Benefit 1 5.5 0.5
Competitor 3 Benefit 1 7.5 8
Max Benefit 1 7.5 0
Competitor 1 Benefit 2 4.5 0.5
Competitor 3 Benefit 2 6 2
Max Benefit 2 6 0

I imagine this could be done in one SQL query. I'd appreciate any help.

Upvotes: 1

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Hmmm . . . You can construct the result set using union all. The subquery in the outer select makes it easier to ensure the final ordering:

with cte as (
      select company, benefit, avg(perVal) AS P, var(perVal) AS V
      from Percept_Responses
      group by company, benefit
     )
select company, benefit, p, v
from ((select company, benefit, p, v, 1 as ord
       from cte
      ) union all
      (select 'Max', benefit, max(p), 0, 2 as ord
       from cte
       group by benefit
      )
     ) cb
order by benefit, ord;

Upvotes: 1

Related Questions