How do I combine multiple counts in a single SQL query

Question

I'd like to be able to write a single SQL query to extract data about clubs and teams from my database. Let's say I have a simple schema represented by the following pseudo-code:

TABLE CLUB
  ID int
  NAME varchar
  ...

TABLE TEAM
  ID int
  NAME varchar
  CLUB_ID int -- foreign key into CLUB
  PAID_UP boolean
  ...

I'd like to write a single query that would give me the count of teams in a club, the number of teams that are paid up, and one vs the other expressed as a percentage.

I can get the number of teams per club using:

SELECT CLUB.id, CLUB.name, COUNT(TEAM.id) AS "#TEAMS"
FROM CLUB
INNER JOIN TEAM
ON CLUB.ID = TEAM.CLUB_ID
GROUP BY CLUB.ID

I can modify that query to get the number of paid up teams per club using:

SELECT CLUB.id, CLUB.name, COUNT(TEAM.id) AS "#PAID UP TEAMS"
FROM CLUB
INNER JOIN TEAM
ON CLUB.ID = TEAM.CLUB_ID
WHERE TEAM.PAID_UP = true
GROUP BY CLUB.ID

But how do I write a single query that combines both (and expresses the latter as a percentage). For example, to produce output such as:

CLUB    #TEAMS  #PAIDUP  %PAIDUP
Eagles       4        3      75%
Cobras      10        6      60%
...

Could you please paste your sample data – Atif Aug 31 '20 at 04:32 — Atif, Aug 31 '20 at 04:32

Abra · Accepted Answer · 2020-08-31T07:21:25.653

Untested.

SELECT CLUB.id
      ,CLUB.name
      ,COUNT(TEAM.id) AS "#TEAMS"
      ,SUM( case TEAM.PAID_UP
                when true then 1
                else 0
              end case) AS "#PAID UP TEAMS"
      ,"#TEAMS" / "#PAID UP TEAMS" * 100 AS "%PAIDUP"
FROM CLUB
INNER JOIN TEAM
ON CLUB.ID = TEAM.CLUB_ID
GROUP BY CLUB.ID

If it's wrong, I'll delete it.

EDIT

Based on the comment to this answer, you could simply repeat the column expressions.

SELECT CLUB.id
      ,CLUB.name
      ,COUNT(TEAM.id) AS "#TEAMS"
      ,SUM( case TEAM.PAID_UP
                when true then 1
                else 0
              end case) AS "#PAID UP TEAMS"
      ,COUNT(TEAM.id) / SUM( case TEAM.PAID_UP
                when true then 1
                else 0
              end case) * 100 AS "%PAIDUP"
FROM CLUB
INNER JOIN TEAM
ON CLUB.ID = TEAM.CLUB_ID
GROUP BY CLUB.ID

It's not bad. I found that I needed to change `COUNT` to `SUM` for the paid up teams calculation to work. However, I could get the percentage to work as it fails with `ERROR: column "#TEAMS" does not exist`. Any thoughts on how to fix that? — dave, Aug 31 '20 at 05:44

score 1 · Answer 2 · answered Aug 31 '20 at 06:04

Based on @Abra's answer, we have the two COUNTs and based on this question we have the calculation. The latter SELECTs from the former.

SELECT "ID", "NAME", "#TEAMS", "#PAID_UP",
       100 * "#PAID_UP" / "#TEAMS" AS "%PAID_UP" FROM (
    SELECT CLUB.id AS "ID",
        CLUB.name AS "NAME",
        COUNT(TEAM.id) AS "#TEAMS"
        SUM(CASE TEAM.PAID_UP
                WHEN true THEN 1
                ELSE 0
            END) AS "#PAID_UP"
    FROM CLUB
    INNER JOIN TEAM
    ON CLUB.ID = TEAM.CLUB_ID
    GROUP BY CLUB.ID
) AS "INNER"

How do I combine multiple counts in a single SQL query

2 Answers2

EDIT