用于计数的数据库查询

Question

我有一张桌子

ID  |   NAME    |   DEPT
1   |   A       |   D-1
2   |   B       |   D-1
3   |   C       |   D-2
4   |   D       |   D-3
5   |   E       |   D-1
6   |   F       |   D-3
7   |   G       |   D-4

现在，我想获取如下结果

DEPT    |   COUNT(DEPT)
D-1     |   3
OTHERS  |   4

有人能帮我写这个查询吗？

Answer 1

您需要使用CASE按D-1和Others对数据进行分组

select 
  case when dept = 'D-1' then 'D-1' else 'Others' end as Dept,
  count(*) as Total
from yt
group by case when dept = 'D-1' then 'D-1' else 'Others' end;

请参阅SQL Fiddle with Demo

Answer 2

我猜你想要最大发生部门与计数和其他部门与休息部门发生的总和。

你可以像下面这样做-

Select dept, count(*) cnt  
  from Table_name
 group by dept
having count(*) = ( select max(count(*)) from table_name group by dept)
union
select 'Other' dept, count(*) cnt 
  from table_name 
 where dept not in ( select dept 
                       from table_name 
                      group by dept 
                     having count(*) = ( select max(count(*)) 
                                           from table_name 
                                          group by dept
                                        )
                   )

我希望这就是你要找的..

Answer 3

SELECT 
     DEPT,
     COUNT (DEPT)AS [COUNT(DEPT)]
FROM(SELECT 
          (CASE DEPT WHEN 'D-1' THEN 'D-1' ELSE 'OTHERS' END) AS DEPT FROM test1) AS tbl
GROUP BY DEPT

请尝试上面的sql查询。