CODEWITHSUNDEEP
sql
dave
dave

Reputation: 11975

How do I combine multiple counts in a single SQL query

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%
...

Upvotes: 0

Views: 50

Answers (2)

Abra
Abra

Reputation: 20914

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

Upvotes: 2

dave
dave

Reputation: 11975

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"

Upvotes: 1

Related Questions