sql - 在sql中计算总数

Question

我有一张如下表，我想计算发给每个局的票数。

      Title    No_ticket    Directorate1     Direcotorate2  Direcotorate3    Direcotorate4   Direcotorate5 
   Conference   2           Marketing            Finance
      Training  2               IT               Finance
      Training  2               IT               Marketing

我想看到输出为

 Directorate  Total ticket 
  Marketing       2
    IT            2
   Finance        2

我怎样才能做到这一点。对此的任何帮助将不胜感激。

我的脚本是

select Count(t.no_ticket),directorate1 
from #ticketbooking t
group by t.no_ticket,directorate1

union

select Count(t.no_ticket),directorate2 
from #ticketbooking t
group by t.no_ticket,directorate2

哪个没有给我我想要的

标签： sqlsql-servertsql

Answer 1

您可以尝试使用 union all 和 subquery

select Directorate ,count(*) as [Total ticket]
from
(
   select Directorate1 as Directorate from tablename where Directorate1  is not null
   union all
   select Directorate2 from tablename where Directorate2  is not null
   union all
   select Directorate3 from tablename where Directorate3  is not null
   union all
   select Directorate4 from tablename where Directorate4  is not null
   union all
   select Directorate5 from tablename where Directorate5  is not null
)A group by Directorate