如何使用一个 SQL 查询获得多个计数? [英] How to get multiple counts with one SQL query?

问题描述

我想知道如何编写此查询.

I am wondering how to write this query.

我知道这个实际的语法是假的，但它会帮助你理解我想要什么.我需要这种格式，因为它是更大查询的一部分.

I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query.

SELECT distributor_id, 
COUNT(*) AS TOTAL, 
COUNT(*) WHERE level = 'exec', 
COUNT(*) WHERE level = 'personal'

我需要在一个查询中全部返回.

I need this all returned in one query.

另外，它需要在一行中，所以以下不起作用:

Also, it need to be in one row, so the following won't work:

'SELECT distributor_id, COUNT(*)
GROUP BY distributor_id'

推荐答案

您可以将 CASE 语句与聚合函数一起使用.这与某些 RDBMS 中的 PIVOT 函数基本相同:

You can use a CASE statement with an aggregate function. This is basically the same thing as a PIVOT function in some RDBMS:

SELECT distributor_id,
    count(*) AS total,
    sum(case when level = 'exec' then 1 else 0 end) AS ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id

这篇关于如何使用一个 SQL 查询获得多个计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！