Jump to content

Counting Medals Across Different Tables Unique To Each Person


perksy

Recommended Posts

Hi everyone,

 

I've been wrapping my head around this problem and I was wondering if you could help me.

 

I have the following three tables with example data:

 

 

results_patterns

===========================

 

name | medal

-------- | ------------

James | Gold

James | Silver

James | Silver

Paul | Gold

Sam | Bronze

Sarah | Silver

 

 

 

results_sparring

===========================

 

name | medal

-------- | ------------

James | Bronze

James | Bronze

James | Bronze

Paul | Silver

Sam | Gold

Sarah | Silver

 

 

results_patterns

===========================

 

name | medal

-------- | ------------

James | Silver

James | Silver

James | Gold

Paul | Gold

Sam | Bronze

Sarah | Silver

 

 

There are other columns in the tables such as date, age, category etc but these are not necessary. The idea being that there can be duplicate medals and names in each table (due to different competition dates)

 

 

I'm trying to get a medal count for each individual person, and produce an sql statement to get a result like this:

 

 

 

results

===========================

 

 

name | gold | silver | bronze

---------|----------|-----------|-----------

James | 2 | 4 | 3

Paul | 2 | 1 | 0

Sam | 1 | 0 | 2

Sarah | 0 | 3 | 0

 

 

 

I hope it's clear what I'm after; I'll happily explain more if required.

 

I've tried UNION ALL three tables for WHERE 'Gold' medals. I wanted to count how many gold medals per person but I failed at this stage. I was then going to perhaps JOINT LEFT this results with the same for each Silver and Bronze. But I have no idea how to go about it.

 

Any help would be greatly appreciated as I'm a noob with MySQL.

 

Thanks very much :)

Link to comment
Share on other sites

SELECT name, SUM(gold) as Gold, SUM(silver) as Silver, SUM(bronze) as Bronze
FROM (
   SELECT name,
    IF (medal='Gold', 1,0) as gold,
    IF (medal='Silver', 1,0) as silver,
    IF (medal='Bronze', 1,0) as bronze
   FROM results_patterns
   UNION ALL
   SELECT name,
    IF (medal='Gold', 1,0) as gold,
    IF (medal='Silver', 1,0) as silver,
    IF (medal='Bronze', 1,0) as bronze
   FROM results_sparring
   ) as medals
GROUP BY name;

 

results:

 

+-------+------+--------+--------+
| name  | Gold | Silver | Bronze |
+-------+------+--------+--------+
| James |    1 |	  2 |	  3 |
| Paul  |    1 |	  1 |	  0 |
| Sam   |    1 |	  0 |	  1 |
| Sarah |    0 |	  2 |	  0 |
+-------+------+--------+--------+

Link to comment
Share on other sites

Wow! Thanks so much for your help, it works flawlessly! It's so much simpler than I thought it was going to be as well. I'm now going to do a bit more reading about what is exactly going on but I think I get it.

 

Thanks once again :D

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.