CODEWITHSUNDEEP
mysqlsqlsubquery
Corey Thompson
Corey Thompson

Reputation: 398

Subquery in SQL

So I have an Olympic database, the basic layout is that there's a competitors table with competitornum, givenname, and familyname (other columns aren't necessary for this) There's also a results table with competitornum, and place (between 1 and 8).

I'm trying to get the givenname and familyname and total number of gold, silver, and bronze medals (place = 1, 2 or 3)

Here's what I've got so far:

SELECT c.Givenname, c.Familyname, places AS TotalPlaces
FROM Competitors c,
        (SELECT COUNT(*) as places 
         FROM Results r, Competitors c 
         WHERE r.Competitornum = c.Competitornum
         AND r.Place > 0
         AND r.Place < 4) q

However it returns everyone's name, and the same total places (which happens to be 78). e.g.

John Smith 78
Cassandra Jane 78
Bob Turner 78

Upvotes: 2

Views: 164

Answers (1)

John Woo
John Woo

Reputation: 263693

Currently your query is conducting CROSS JOIN producing caertesian product.

When using aggregate function (count, max, min,...), the records should be group by non-aggregated column. Try this,

SELECT c.Givenname, c.Familyname, COUNT(r.places) AS TotalPlaces
FROM Competitors c INNER JOIN Results r
        ON r.Competitornum = c.Competitornum
WHERE r.place IN (1,2,3)
GROUP BY c.Givenname, c.Familyname

Upvotes: 1

Related Questions