As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

SQL count across multiple columns

LewishamLewisham Registered User regular
edited April 2009 in Help / Advice Forum
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.

Lewisham on

Posts

  • EchoEcho ski-bap ba-dapModerator mod
    edited April 2009
    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 on
  • EchoEcho ski-bap ba-dapModerator mod
    edited April 2009
    Threw together a quick test database:

    sql-data.png

    And that did indeed work:

    sql-result.png

    Apply your hideous unions as necessary. :P

    Echo on
  • LewishamLewisham Registered User regular
    edited April 2009
    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!

    Lewisham on
  • vonPoonBurGervonPoonBurGer Registered User regular
    edited April 2009
    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.

    vonPoonBurGer on
    Xbox Live:vonPoon | PSN: vonPoon | Steam: vonPoonBurGer
  • LewishamLewisham Registered User regular
    edited April 2009
    Alright, alright, I'll move the data across to a new schema today :)

    Lewisham on
  • SmasherSmasher Starting to get dizzy Registered User regular
    edited April 2009
    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?

    Smasher on
  • LewishamLewisham Registered User regular
    edited April 2009
    Yes, items are stored in a separate table joined by the (Blizzard) item ID, so you can have the same item in multiple slots.

    Lewisham on
Sign In or Register to comment.