利用聚合函数GROUP BY ROLLUP 添加一行合计数据

select case when qugroupc=0 and mgroupc=1 then CONCAT(substring( REVERSE(qu),1,1),'','季度')
            when qugroupc=1 and mgroupc=1 then '年度'
                        else m end m,count
from (

select  CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE))    qu,DATE_FORMAT(DIAG_DATE,'%Y%m')  m,count(1) count,
         grouping(CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE))) qugroupc,grouping(DATE_FORMAT(DIAG_DATE,'%Y%m'))mgroupc
  from s_chronic
 
WHERE DIAG_DATE BETWEEN '2021-01-01' AND  now() group by qu,m WITH ROLLUP
) as t

 

 

效果图：

 

 子查询 

select  CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE))    qu,DATE_FORMAT(DIAG_DATE,'%Y%m')  m,count(1) count,
         grouping(CONCAT(YEAR(DIAG_DATE),'_',quarter(DIAG_DATE))) qugroupc,grouping(DATE_FORMAT(DIAG_DATE,'%Y%m'))mgroupc
  from s_chronic
 
WHERE DIAG_DATE BETWEEN '2021-01-01' AND  now() group by qu,m WITH ROLLUP

的效果图：