CODEWITHSUNDEEP
sqlsqliteselect
Qudit
Qudit

Reputation: 1

SQL: How many rows have a given column as their max (from a list of columns)

I've just started learning SQL (specifically SQLite) and I've been playing with this data.

Every Marvel character in this list has rating for each attribute (strength, intelligence, speed, etc.) I want to find how many characters have each attribute as their greatest. For example, how many characters have strength as their greatest attribute?

We can assume that if a character has two or more attributes as their highest, that character will count towards each attribute.

The output I'm expecting is something like:

best_Attribute      COUNT(*)
__________________________________
intelligence            a
strength                b
speed                   c
durability              d
energy_Projection       e
fighting_Skills         f

Here's my attempt:

SELECT COUNT(*),
    CASE
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == intelligence THEN "intelligence"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == strength THEN "strength"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == speed THEN "speed"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == durability THEN "durability"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == energy_Projection THEN "energy_Projection"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == fighting_Skills THEN "fighting_Skills"
    END as "best_Attribute"
FROM marvels
GROUP BY best_Attribute;

This is ugly, verbose and doesn't even work. Here is the output:

COUNT(*)    best_Attribute
__________________________
    2               4
    3               6
    7               7

Is there a (nice) way of getting the desired output?

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Tim's solution is the most performant method -- and how you were approaching the problem. However, another method is to unpivot the data and use aggregation. I just want to offer this as an alternative so you can see different ways of solving the problem in SQL:

with a as (
      select name, 'intelligence' as attribute, intelligence as val
      from marvels
      union all
      select name, 'strength' as attribute, strength as val
      from marvels
      union all
      select name, 'speed' as attribute, speed as val
      from marvels
      union all
      select name, 'durability' as attribute, durability as val
      from marvels
      union all
      select name, 'energy_Projection' as attribute, energy_Projection as val
      from marvels
      union all
      select name, 'fighting_Skills' as attribute, fighting_Skills as val
      from marvels
     )
select a.attribute, count(*) as num_with_max
from a
where a.val = (select max(a2.val) from a a2 where a2.name = a.name)
group by a.attribute;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521314

I'm not sure if you need your exact suggested output. I can offer the following query, which would report the attribute counts over the entire table as separate columns.

SELECT
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = intelligence THEN 1 END) AS intelligence_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = strength THEN 1 END) AS strength_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = speed THEN 1 END) AS speed_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = durability THEN 1 END) AS durability_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = energy_Projection THEN 1 END) AS energy_Projection_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = fighting_Skills THEN 1 END) AS fighting_Skills_count
FROM marvels;

Note that you don't need to use GROUP BY here if you want totals over the entire table.

Upvotes: 2

Related Questions