CODEWITHSUNDEEP
sqlsql-servert-sql
emie
emie

Reputation: 289

SQL Query with CASE and MAX value

Customers have the ability to purchase multiple products, and I have created the query below that calculates the usage for each customer product by Workload. And it works.

However, how do I go about selecting the Workload with the highest Utilization?I've tried multiple ways but it doesn't appear to be working.

Here is what I get: enter image description here

Here is what I want: enter image description here

with Usage as
    (
        select 
            [ID]
            ,[Workload]
            ,[QE]
            ,[EE]
            ,(CASE 
                WHEN QE <= 0 THEN '0'
                ELSE CAST([AE] as float) / [QE]
            END) as Utilization
        from [source].[Usage]
    )

select 
    O.[ID]
    ,O.[Workload]
    ,O.[QE]
    ,O.[EE]
    ,max(cast([Utilization] as decimal)) as 'Utilization'
FROM Usage O
Group by O.[ID], O.[Workload], O.[QE],O.[EE]

Upvotes: 0

Views: 8758

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Just use window functions. Here is one way:

with Usage as (
      select [ID], [Workload], [QE], [EE],
             MAX(CASE WHEN QE <= 0 THEN 0 ELSE CAST([AE] as float) / NULLIF([QE], 0)
                 END) as Utilization
      from [source].[Usage] u
      group by [ID], [Workload], [QE], [EE]
     ) 
select u.*
from (select u.*, row_number() over (partition by id order by utilization desc) as seqnum
      from Usage u
     ) u
where seqnum = 1;

Upvotes: 2

Related Questions