SELECT SUM - SQL query not working

Question

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

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:

Jou need to join medal and athlet LEFT JOIN Medal ON athlet.MedalID= Medal. MedalID — Stefan Avramovic
– Stefan Avramovic, Commented Mar 11, 2018 at 20:50
You'll also need a GROUP BY clause, as well as the tyoe of medal in the SELECT — Jonnix
– Jonnix, Commented Mar 11, 2018 at 20:51
On your or die you really want to be looking at mysqli_error to see what the actual problem is rather than guessing. — Jonnix
– Jonnix, Commented Mar 11, 2018 at 20:56

SaidbakR · Accepted Answer · 2018-03-11 21:12:09Z

1

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

edited Mar 11, 2018 at 21:12

answered Mar 11, 2018 at 20:56

SaidbakR

13.6k23 gold badges113 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aaron F Over a year ago

Thanks for your input! I'm beginning to see the sense in your approach. I have amended it slightly to match my MedalIDs. They are 401, 402, 403 for Gold, Silver and Bronze respectively as you suggested. Please see my answer again. I have updated with a screenshot of my result. It appears to only display 1 country and no medals

SaidbakR Over a year ago

You did not make a valid loop through your result set. For now, just try the code in some tool such as phpMyAdmin and you will see the correct result, by this way, the SQL problem is solved, after that, search to know how to execute SQL queries using PHP and MySQL and render the obtained result.

Aaron F Over a year ago

thanks! I just ran the code in phpmyadmin and that works. I'm looking into a method now of creating a loop to return the values of all countries

Mohammad · Accepted Answer · 2018-03-11 22:13:25Z

0

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

edited Mar 11, 2018 at 22:13

answered Mar 11, 2018 at 21:24

Mohammad

7397 silver badges23 bronze badges

1 Comment

Aaron F Over a year ago

this doesn't seem to work. shouldn't you be selecting FROM athlete as a ?

pgngp · Accepted Answer · 2018-03-11 22:42:32Z

0

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.

edited Mar 11, 2018 at 22:42

answered Mar 11, 2018 at 20:59

pgngp

1,5805 gold badges16 silver badges27 bronze badges

4 Comments

Aaron F Over a year ago

still getting "Invalid Query"

pgngp Over a year ago

Could you print out the actual error string using something like die(mysqli_error($conn))?

Aaron F Over a year ago

your code is working 100% for me in phpmyadmin but when I try to run is in php is doesn't seem to work! I'm getting this error: Parse error: syntax error, unexpected 'Gold' (T_STRING)

pgngp Over a year ago

When you put it in php, escape the quotes using \' and \" or use a built-in php function to do this.

Collectives™ on Stack Overflow

SELECT SUM - SQL query not working

3 Answers 3

Update

3 Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Update

3 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related