代码之家 › 专栏 › 技术社区 › dave

如何在一个SQL查询中组合多个计数

sql

dave · 技术社区 · 5 年前

我希望能够编写一个SQL查询,从我的数据库中提取有关俱乐部和球队的数据。假设我有一个简单的模式,由以下伪代码表示:

TABLE CLUB
  ID int
  NAME varchar
  ...

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

我可以通过以下方法获得每个俱乐部的球队数量:

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

我可以通过以下方式修改该查询以获得每个俱乐部的付费球队数量:

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

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

1 回复 | 直到 5 年前

Abra BlueJK 5 年前

未经测试。

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

如果错了,我就删掉。

编辑

根据对此答案的注释,您可以简单地重复列表达式。

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

dave 5 年前

COUNT this question 我们有计算。后者 SELECT 是前者的。

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"

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

1 年前

John D · 需要为NULL或NOT NULL的WHERE子句

1 年前

ojek · 如何对SQL结果进行分组和编号?

1 年前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

1 年前

Sax · 规范化Google表格(第一步)

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

1 年前