CODEWITHSUNDEEP
sqlpostgresql
ceno980
ceno980

Reputation: 2011

SQL: Calculate the number of red and yellow cards for each country

I have the following table called Players:

name      |    memberof   |    country   |   givento   |   cardtype
Ni             1               Australia     7             red
Ju             1               Australia     9             yellow
Is             1               Australia     11            red
Ji             3               Japan         2             red
Su             3               Japan         22            red
Sa             3               Japan         25            yellow
Ji             3               Japan         2             yellow
Ji             3               Japan         2             yellow
Li             4               Thailand      13            red
Li             4               Thailand      13            yellow
Ro             1               Australia     null          null
So             2               Malaysia      null          null

The above table shows the players and the cards they have received in a football match. Name refers to the name of the player, memberof refers to the id of the team, country is the country the player belongs to, givento is the id of the player, and cardtype can be either red or yellow.

I am trying to write a query that determines the total number of red and yellow cards received by each country. A country is considered to receive a card if one of its players receives one.

The output for the query should be:

teams      |   reds    |   yellows
Australia       2           1
Malaysia        0           0
Japan           2           3
Thailand        1           1

I have the following two views, which give me the total number of yellow and red cards each country has received respectively:

create or replace view yellow as select memberof, country, cardtype, 
count(cardtype) over (partition by country) 
as yellows from Players where cardtype = 'yellow';


 create or replace view red as select memberof, country, cardtype, 
 count(cardtype) over (partition by country) 
 as reds from Players where cardtype = 'red';

By performing an inner join on the result sets on 'memberof' for the above two queries I can get the total number of red and yellow cards received by a country.

select distinct y.country, r.reds, y.yellows from yellow y inner join red r on 
y.memberof = r.memberof;

However, Malaysia, which has received zero red and zero yellow cards is not included in the output.

    country  |    reds    |   yellows
   Australia      2            1
   Japan          2            3
   Thailand       1            1

How can I count the red and yellow cards for countries who have received zero cards of either colour? Any insights are appreciated.

Upvotes: 0

Views: 216

Answers (3)

Jon
Jon

Reputation: 31

I like Gordon's answer, though an alternative could be to sum a case statement. This may be helpful if you require compatibility, though I would assume more resource intensive.

SELECT country,
    SUM(
        CASE WHEN cardtype = 'red' OR cardtype = 'yellow' THEN 1
            ELSE 0 
        END
    ) AS CardCount

FROM players
GROUP BY country

Upvotes: 0

forpas
forpas

Reputation: 164099

It is simple conditional aggregation:

select 
  country, 
  sum(case when cardtype = 'red' then 1 else 0 end) reds,
  sum(case when cardtype = 'yellow' then 1 else 0 end) yellows 
from players 
group by country

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Use filter and just plan aggregation:

select p.country,
       count(*) filter (where p.carttype = 'red') as reds,
       count(*) filter (where p.carttype = 'yellow') as yellows
from players p
group by p.country;

Your approach with two views and window functions seems way to complicated.

Upvotes: 4

Related Questions