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

问题描述

我想知道如何写这个查询。

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

  SELECT distributor_id，
 COUNT（*）AS TOTAL，
 COUNT（*）WHERE level ='exec'，
 COUNT（*）WHERE level ='personal'
  / pre> 
 
 
我需要在一个查询中返回这个。 
 
 
 
此外，它需要在一行中，因此以下内容不起作用：
 'SELECT distributor_id，COUNT（*）
 GROUP BY distributor_id'
  
 
 
解决方案
您可以使用带有聚合函数的 CASE 语句。这基本上与某些RDBMS中的 PIVOT 函数相同：
  select bos（）当然，如果level ='个人'，那么这个值就是1。 else 0 end）PersonalCount 
 from yourtable 
 group by distributor_id 
  
 
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'

解决方案
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(*) total,
    sum(case when level = 'exec' then 1 else 0 end) ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) PersonalCount
from yourtable
group by distributor_id


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