Counting Medals Across Different Tables Unique To Each Person

[perksy]

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

[Barand]

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 |
+-------+------+--------+--------+

[perksy]

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