CODEWITHSUNDEEP
sqldatabasesqlite
JLW
JLW

Reputation: 9

sqlite3: COUNT & EXCEPT not working as expected

I'm fairly new to SQL but having searched the internet for an answer to this I still cannot get my COUNT and EXCEPT statements to select what I want.

My Database:

sqlite> CREATE TABLE Football(Team TEXT, Player TEXT, Age INTEGER, primary key(Team, Player));
sqlite> .separator ,
sqlite> .import databaseTest Football
sqlite> .headers on
sqlite> .mode col
sqlite> SELECT Team, Player, Age FROM Football ORDER BY Team;
Team        Player      Age       
----------  ----------  ----------
Arsenal     Cech        38        
Arsenal     Giroud      29        
Arsenal     Sanchez     28        
Arsenal     Walcott     27        
Chelsea     Costa       29        
Chelsea     Courtois    25        
Chelsea     Hazard      26        
Chelsea     Willian     26        
Liverpool   Can         23        
Liverpool   Coutinho    24        
Liverpool   Wjinaldum   25        
Liverpool   Woodburn    17        
Manchester  Aguero      29        
Manchester  Jesus       19        
Manchester  Silva       28        
Manchester  Toure       34        
Manchester  De Gea      26        
Manchester  Felliani    29        
Manchester  Rooney      32        
Manchester  Schweinste  35        
Tottenham   Delle Ali   22        
Tottenham   Kane        24        
Tottenham   Rose        24        
Tottenham   Vertonghen  27

What I want to do is SELECT the COUNT of teams that do not have a player over the age of 30. So the select statement should be 3 (Chelsea, Liverpool, Tottenham).

This is the statement I've tried and assumed would work:

sqlite> SELECT COUNT(DISTINCT Team) FROM Football
...> EXCEPT 
...> SELECT COUNT(DISTINCT Team) FROM Football WHERE Age > 30;
COUNT(DISTINCT Team)
--------------------
6

But as you can see it returns '6'. What am I doing wrong and how can I get the correct result?

Upvotes: 0

Views: 678

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Here is another way. Look at the maximum age for each team:

SELECT COUNT(*)
FROM (SELECT Team
      FROM Football
      GROUP BY Team
      HAVING MAX(Age) <= 30
     ) t;

You can also use EXCEPT, but this also requires a subquery. You need to do the set operation before doing the count:

SELECT COUNT(DISTINCT TEAM)
FROM (SELECT Team FROM Football
      EXCEPT 
      SELECT Team FROM Football WHERE Age > 30
     ) t;

Strictly speaking, this query could use COUNT(*) rather than COUNT(DISTINCT). However, it can be troublesome to remember that EXCEPT (like UNION) removes duplicate values.

Upvotes: 1

Related Questions