'What is the difference when using a case statement or max before the case statement?

I just started as a data analyst and have a question on case statements. Specifically, I have two stored procedures that pull video customers so the company can count current active accounts. I noticed in one of the queries, there is a piece of code that starts with Max(case etc). The specific code is as follows.

`MAX(CASE

WHEN PC.ComponentCode =    'VVBS'    THEN 'Bronze - Legacy'
WHEN PC.ComponentCode =    'VVEBS'   THEN 'Silver - Legacy'
WHEN PC.ComponentCode
LIKE 'VVDPS%'  THEN 'Gold - Legacy'
WHEN PC.ComponentCode =    'BRNZSEL' THEN 'Bronze - Select'
WHEN PC.ComponentCode =    'SLVRSEL' THEN 'Silver - Select'
WHEN PC.ComponentCode =    'GOLDSEL' THEN 'Gold - Select'
WHEN PC.ComponentCode =    'BRZMOBI' THEN 'Bronze - Mobi'
WHEN PC.ComponentCode =    'SLVMOBI' THEN 'Silver - Mobi'
WHEN PC.ComponentCode =    'GLDMOBI' THEN 'Gold - Mobi'
ELSE NULL END) as video`

I am trying to understand why the developer of the code used a max here? When I took it out, I received a message saying I needed to group video where it wasn't grouped before. The numbers are different though as expected, I'm just struggling to understand the role of the max.

netezza


Solution 1:[1]

PC.ComponentCode is only going to match on one of those conditions (for any given row).

But because the developer is doing aggregations against some other columns (group / sum / count / ???) then THIS COLUMN must also be aggregated. Otherwise, you'll get that error saying it needs to be grouped.

So this is just a trick to sidestep that error message. The end result should be the same.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mark F

Related Questions