如何使用聚合函数获取SUM和COUNT

Question

我有像下面这样的桌子

SCHEME  BROKNO  BRANNO  ACCNO   TYPE    SCAMT   FUNAMT  MONTH
AFS       123    345    125635  RN       130     140    11
AFS       123    345    125635  RN       130     140    11
AFS       123    345    125635  RN       145     185    11
AFS       123    345    125635  RN       130     140    11
AFS       123    345    125635  RN       180     220    10
AFS       123    345    125635  RN       200     260    10
AFS       123    345    125635  RN       180     220    10
AFS       123    345    125635  RN       180     220    10

现在我必须创建基于月列和组(SCHEME，BROKNO，BRANNO，ACCNO，TYPE)的报告。

报告它应该是

SCHEME BROKNO BRANNO ACCNO TYPE SUM(SCAMT) SUM(FUNAMT) MONTH11CNT   
AFS    123    345   125635 RN    535            605      4         

SUM(SCAMT) SUM(FUNAMT) MONTH11CNT 
740         920           4

我已经尝试了case语句，但我得到了所有记录组的相同计数。请谁给我点子。

Answer 1

看起来您只是想按方案、brokno、branno、accno、type和month进行分组：

select 
  scheme, brokno, branno, accno, type, month, 
  sum(scamt), sum(funamt), count(*) as monthcnt
from mytable
group by scheme, brokno, branno, accno, type, month;

编辑:如果你想在列中显示月份，你必须事先知道月份，并相应地编写查询：

select 
  scheme, brokno, branno, accno, type,
  sum(case when month = 10 then scamt end) as month10scamt,
  sum(case when month = 10 then funamt end) as month10funamt,
  sum(case when month = 10 then 1 end) as month10cnt,
  sum(case when month = 11 then scamt end) as month11scamt,
  sum(case when month = 11 then funamt end) as month11funamt,
  sum(case when month = 11 then 1 end) as month11cnt
from mytable
where month in (10,11)
group by scheme, brokno, branno, accno, type;

Answer 2

您可以使用它来获得您想要的结果

SELECT 
  SCHEME,
  BROKNO,
  BRANNO,
  ACCNO,
  TYPE,
  SUM(SCAMT) As SCAMTTOTAL,
  SUM(FUNAMT) AS FUNAMTTOTAL,
  MONTH As MONTHNAME,
  COUNT(MONTH) AS MONTHCOUNT
FROM MyTABLE
GROUP BY
  SCHEME,
  BROKNO,
  BRANNO,
  ACCNO,
  TYPE,
  MONTH

如果您需要单行中的结果，那么只需将其转换为CTE并进行查询即可。

SQLFIDDLE

Answer 3

假设您想要计算每个月内的行数，一种解决方案是将该月也纳入聚合：

select SCHEME, BROKNO, BRANNO, ACCNO, TYPE, MONTH,
       sum(SCAMT),
       sum(FUNAMT),
       Count(*) as MONTHCOUNT
from table
group by SCHEME, BROKNO, BRANNO, ACCNO, TYPE, MONTH