CODEWITHSUNDEEP
phpmysql
Aaron F
Aaron F

Reputation: 33

SELECT SUM - SQL query not working

I've created a small database based on the Olympics for running a few sql queries from. I am trying to generate a table that displays each Country name and the total of Gold, Silver & Bronze medals won based on Athletes that are from these countries. Here is a screenshot of my Relational Model View:

Relational Model View enter image description here

Here is my query I am trying to run:

<h1>Olympics Database</h1>
<h3>Summary Information</h3>

    <h4>Number of Olympic Athletes from United Kingdom</h4>

<?php

    $conn = mysqli_connect('localhost', '#####', '#####') or die ('Could not connect:' . mysqli_error($conn));
    echo 'Successfully Connected. <br/>';

    mysqli_select_db($conn, '#####') or die('Database will not open');
    echo 'Database Connected. <br/> <br/>';

    $query2 = 'SELECT CountryAbbrev, (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 401 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Gold, (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 402 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Silver, (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 403 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Bronze FROM athlete AS ath';
    $result2 = mysqli_query($conn, $query2) or die ('Invalid Query');

    echo'<table><tr><th>Country Name</th><th>Gold</th><th>Silver</th><th>Bronze</th></tr>';
    $row = mysqli_fetch_row($result2);
    echo'<tr><td>' .$row[0].'</td><td>' .$row[1].'</td><td>' .$row[2].'</td><td>' .$row[3].'</td></tr>';
    echo'</table>';

    mysqli_close($conn);
    ?>

All I'm getting is invalid query. Going by my relation model view, could anyone help me figure out how I can display the total of gold, silver and bronze medals won by each country in a single table?

UPDATED: Here is a screenshot of the output: enter image description here

Upvotes: 2

Views: 545

Answers (3)

pgngp
pgngp

Reputation: 1562

In order to fix the "invalid query", you need a left join with medal table, change the ANDs to ORs, and use a group by clause. Also, since you need the medal count, you need to replace SUM with COUNT. So you can do something like this:

SELECT CountryAbbrev as country, count(athlete.MedalID) 
FROM athlete 
  LEFT JOIN medal on medal.MedalID = athlete.MedalID
WHERE MedalName = "Gold" 
  OR MedalName = "Silver" 
  OR MedalName = "Bronze"
GROUP BY CountryAbbrev;

This query outputs the total medal count (# gold + # silver + # bronze) for each country:

+---------+------------------------+
| country | count(athlete.MedalID) |
+---------+------------------------+
| AU      |                      4 |
| US      |                      5 |
+---------+------------------------+

If you want to print out the count of gold, silver, and bronze individually in difference columns, then you can use following query:

SELECT CountryAbbrev as country, 
  sum(if(MedalName = "Gold", 1, 0)) as Gold, 
  sum(if(MedalName = "Silver", 1, 0)) as Silver, 
  sum(if(MedalName = "Bronze", 1, 0)) as Bronze 
FROM athlete 
  LEFT JOIN medal on medal.MedalID = athlete.MedalID 
WHERE MedalName = "Gold" 
  OR MedalName = "Silver" 
  OR MedalName = "Bronze" 
GROUP BY CountryAbbrev;

This query will output something like:

+---------+------+--------+--------+
| country | Gold | Silver | Bronze |
+---------+------+--------+--------+
| AU      |    1 |      2 |      1 |
| US      |    2 |      1 |      2 |
+---------+------+--------+--------+

And if you want to add a total column as well, try this query:

SELECT CountryAbbrev as country, 
  sum(if(MedalName = "Gold", 1, 0)) as Gold, 
  sum(if(MedalName = "Silver", 1, 0)) as Silver, 
  sum(if(MedalName = "Bronze", 1, 0)) as Bronze, 
  count(MedalName) as Total 
FROM athlete 
  LEFT JOIN medal on medal.MedalID = athlete.MedalID 
WHERE MedalName = "Gold" 
  OR MedalName = "Silver" 
  OR MedalName = "Bronze" 
GROUP BY CountryAbbrev;

This query will output:

+---------+------+--------+--------+-------+
| country | Gold | Silver | Bronze | Total |
+---------+------+--------+--------+-------+
| AU      |    1 |      2 |      1 |     4 |
| US      |    2 |      1 |      2 |     5 |
+---------+------+--------+--------+-------+

I've ran these queries on my machine and they work as expected.

Upvotes: 0

Mohammad
Mohammad

Reputation: 739

Try this 

SELECT c.CountryAbbrev, 
    (select count(a.MedalID) FROM athlete a WHERE c.CountryAbbrev=a.CountryAbbrev and a.MedalName = "Gold" ) as gold,
    (select count(a.MedalID) FROM athlete a WHERE c.CountryAbbrev=a.CountryAbbrev and a.MedalName = "Silver" ) as Silver,
    (select count(a.MedalID) FROM athlete a WHERE c.CountryAbbrev=a.CountryAbbrev and a.MedalName = "Bronze" ) as Bronze
from country as c

Upvotes: 0

SaidbakR
SaidbakR

Reputation: 13534

Your original SQL query returns invalid query due to you are selecting undefined field in a table. i.e athlete table does not has a field named MedalName.

You may use the following SQL query, by knowing the ids for each medal type, I supposed that they are 1,2,3 for Gold, Silver and Bronze respectively. 

SELECT CountryAbbrev,
 (SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 1 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Gold, 
(SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 2 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Silver, 
(SELECT COUNT(CountryAbbrev) FROM athlete WHERE MedalID = 3 AND ath.CountryAbbrev = athlete.CountryAbbrev) AS Bronze 
FROM athlete AS ath

Update

Sorry, it should be COUNT instead of SUM. Checkout the difference between SUM and COUNT from this link

Upvotes: 1

Related Questions