SQL count across multiple columns

Lewisham

Hi guys,
I've written a program to download data from the World of Warcraft Armory. I have a character table, which stores the character, and what they were wearing at the time. Things that are worn are items, linked by ID to an item table. Each slot represents a different position on the character. As the number of slots is known, I treated it as a one-to-many rather than many-to-many relation.

So we have columns:

Character
======
name
item_slot_0
item_slot_1
item_slot_2
...
item_slot_19

Item
======
item_id
name

What I'd like to do is find the most used item, across all slots. Right now, I have the query for one slot:

select I.name, C.item_slot_0, count(item_slot_0) IS0 
from `CHARACTER` C, ITEM I 
where I.item_id = C.item_slot_0 
group by C.item_slot_0
 order by IS0 desc limit 0,20;

This returns this:

+-----------------------------------+-------------+------+
| name                              | item_slot_0 | IS0  |
+-----------------------------------+-------------+------+
| Greathelm of the Scourge Champion |       38661 | 1487 | 
| Spiked Titansteel Helm            |       41386 | 1090 | 
| Tempered Titansteel Helm          |       41387 |  656 | 
| Hat of Wintry Doom                |       41984 |  360 | 
| Mightstone Helm                   |       37480 |  331 | 
| Circle's Stalwart Helmet          |       27715 |  263 | 
| Reinforced Velvet Helm            |       35572 |  225 | 
| Acherus Knight's Hood             |       34652 |  220 | 
| Whitemane's Chapeau               |        7720 |  219 | 
| Nightscape Headband               |        8176 |  213 | 
| Raging Berserker's Helm           |        7719 |  211 | 
| Invader's Greathelm               |       29946 |  209 | 
| Cowl of the Vindictive Captain    |       44408 |  195 | 
| Hyaline Helm of the Sniper        |       40451 |  190 | 
| Plunderer's Helmet                |       37188 |  177 | 
| Green Tinted Goggles              |        4385 |  175 | 
| Shadowbrim Travel Hat             |       29927 |  174 | 
| Headguard of Retaliation          |       44409 |  172 | 
| Spellstrike Hood                  |       24266 |  165 | 
| The Argent Skullcap               |       44019 |  162 | 
+-----------------------------------+-------------+------+

I'm stumped on how to make it count across all slots. Manually correlating all the data by going item_slot_0, item_slot_1 etc etc is obviously going to work, but laborious and lame! Speed is not an issue, so feel free to use ugly distincts and crazy joins.

Thanks guys!

EDIT: I worked this one out, see if you can do better:

select i.name, count(ai.item) as item_count from
(
    select item_slot_0 as item from `CHARACTER` union all 
    select item_slot_1 as item from `CHARACTER` union all 
    select item_slot_2 as item from `CHARACTER` union all 
    select item_slot_3 as item from `CHARACTER` union all
    select item_slot_4 as item from `CHARACTER` union all
    select item_slot_5 as item from `CHARACTER` union all
    select item_slot_6 as item from `CHARACTER` union all
    select item_slot_7 as item from `CHARACTER` union all
    select item_slot_8 as item from `CHARACTER` union all
    select item_slot_9 as item from `CHARACTER` union all
    select item_slot_10 as item from `CHARACTER` union all
    select item_slot_11 as item from `CHARACTER` union all
    select item_slot_12 as item from `CHARACTER` union all
    select item_slot_13 as item from `CHARACTER` union all
    select item_slot_14 as item from `CHARACTER` union all
    select item_slot_15 as item from `CHARACTER` union all
    select item_slot_16 as item from `CHARACTER` union all
    select item_slot_17 as item from `CHARACTER` union all
    select item_slot_18 as item from `CHARACTER`
) as ai, ITEM i
where i.item_id = ai.item
group by item
order by item_count desc limit 0,20;

It ain't pretty, but it works.

Echo

Oh man, I'm so rusty at SQL. But here we go anyway.

For starters, personally I'd have all item slots as a separate table with character_id, item_slot and item_id, primary key (character_id, item_slot). That gives me all items in one table so I won't have to write humongous joins or unions, and makes it a proper relational database.

I think the following would work on that table. I'm doing it all in my head though and it works there. :P

SELECT COUNT(*),item_id FROM item_slots GROUP BY item_id ORDER BY COUNT(*) DESC

Echo

Threw together a quick test database:



And that did indeed work:



Apply your hideous unions as necessary. :P

Lewisham

Echo wrote: »

Oh man, I'm so rusty at SQL. But here we go anyway.

For starters, personally I'd have all item slots as a separate table with character_id, item_slot and item_id, primary key (character_id, item_slot). That gives me all items in one table so I won't have to write humongous joins or unions, and makes it a proper relational database.

I think the following would work on that table. I'm doing it all in my head though and it works there. :P

SELECT COUNT(*),item_id FROM item_slots GROUP BY item_id ORDER BY COUNT(*) DESC

Yes, that is indeed a possibility. As I described before... the reason I denormalized it that way was because the item slot number doesn't change. 20 was about my limit before I was going to put it into a new table. It makes this particular query pretty gross, but it makes the query to find everything about a character one join less. I'll see how it goes. It's my database and has about 50 000 characters or so in it (right now) so it wouldn't be too difficult to normalize it out again if the query continues to get ugly.

Thanks Echo!

vonPoonBurGer

I'd really suggest restructuring your underlying schema to better support what you want to do. You've got 20 slots for item IDs in a single table, when what you could have is one column for item ID, and a separate column for slot number/name. You'd want this in a separate Equipment table, so you can still have one single row per character in the Character table, but multiple rows per character for gear (e.g. so you can record stats in the Character table without repeating the stats in multiple rows). For example:

SQL> desc character
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 CHARACTER_NAME                                     VARCHAR2(50)
 SERVER_NAME                                        VARCHAR2(30)
 AGILITY                                            NUMBER
 INTELLIGENCE                                       NUMBER
 STAMINA                                            NUMBER
 SPIRIT                                             NUMBER
 STRENGTH                                           NUMBER

SQL> desc equipment
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 CHARACTER_NAME                                     VARCHAR2(50)
 SERVER_NAME                                        VARCHAR2(30)
 ITEM_ID                                            NUMBER
 SLOT_NAME                                          VARCHAR2(20)

SQL> desc items
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ITEM_NAME                                          VARCHAR2(50)
 ITEM_ID                                            NUMBER

This allows you to easily count item IDs across all slots:

select item_name, count(*) from equipment group by item_name;

Or restrict your count to a single slot, or subset of slots:

select item_name, count(*) from equipment where slot_name = 'Head' group by item_name;

select item_name, count(*) from equipment where slot_name in ('Trinket1', 'Trinket2') group by item_name;

It's just a lot easier to deal with the data when it's laid out this way. You can keep Character records unique, Item records unique, and link the two using the separate Equipment table when needed. Within the Equipment table, you may want to enforce per-character slot uniqueness with a compound constraint, i.e. the combination of character name, server name and slot name should be unique within the Equipment table. A single character can't equip multiple helms at the same time, after all, so each character should only be allowed to have one row per equipment slot.

Lewisham

Alright, alright, I'll move the data across to a new schema today

Smasher

My sql-fu is weak, so I can't tell by reading your code Lewis: does that properly handle the same item (namely rings, trinkets, and weapons) being in different slots and/or wearing two of the same item?

Lewisham

Yes, items are stored in a separate table joined by the (Blizzard) item ID, so you can have the same item in multiple slots.