SQL对特定结果使用count或sum

Question

我尝试使用原生SQL查询来获得结果，如下图所示，目前我不确定是否有任何方法可以仅使用SQL来获得此结果。

我已经完成了这个查询，但目前还没有进一步的想法：

 SELECT 
    receipts.client_code clientCode, 
    date_trunc('MON', receipts.create_date) monthYear,
    COUNT(date_trunc('MON', receipts.create_date)) receipts,
    subReceipts.total total
    FROM receipts
    LEFT JOIN (SELECT 
      receipts.client_code clientCode, 
      date_trunc('MON', receipts.create_date) monthYear,
      COUNT(date_trunc('MON', receipts.create_date)) total
      FROM receipts

      GROUP BY 
      receipts.client_code,
      date_trunc('MON' ,receipts.create_date)
      ORDER BY 
      date_trunc('MON' ,receipts.create_date)  
     ) subReceipts ON subReceipts.clientCode = receipts.client_code
   GROUP BY 
    receipts.client_code,
    date_trunc('MON' ,receipts.create_date),
    subReceipts.total 
   ORDER BY 
    date_trunc('MON' ,receipts.create_date)  

示例sql数据和db表创建脚本：

CREATE TABLE receipts 
(
receipt_id int primary key,
client_code varchar not null,
create_date date not null
);

insert into receipts (receipt_id, client_code, create_date) values (1, 'fx90', to_date('2016/01/11', 'yyyy/MM/dd'));
insert into receipts (receipt_id, client_code, create_date) values (2, 'fx90', to_date('2016/02/12', 'yyyy/MM/dd'));
insert into receipts (receipt_id, client_code, create_date) values (3, 'fx90', to_date('2016/02/20', 'yyyy/MM/dd'));
insert into receipts (receipt_id, client_code, create_date) values (4, 'fx90', to_date('2016/03/11', 'yyyy/MM/dd'));
insert into receipts (receipt_id, client_code, create_date) values (5, 'fx90', to_date('2016/03/12', 'yyyy/MM/dd'));
insert into receipts (receipt_id, client_code, create_date) values (6, 'fx90', to_date('2016/03/19', 'yyyy/MM/dd'));

示例结果

​

​

Answer 1

对于postgresql：

SELECT clientCode, monthYear, receipts,
       sum(receipts) over(order by monthYear) as total
  FROM (
     SELECT receipts.client_code clientCode, 
            date_trunc('MON', receipts.create_date) monthYear,
            COUNT(1) receipts
       FROM receipts
      GROUP BY receipts.client_code, monthYear
  ) X
 ORDER BY monthYear

Answer 2

假设mysql，你可以这样做：

set @running_total := 0;
SELECT
    client_code, 
    CONCAT(MONTH(create_date), ' - ', YEAR(create_date)) as month_year,
    COUNT(receipt_id) AS receipts_month,
    (@running_total := @running_total + COUNT(receipt_id)) as total_receipts
FROM receipts
GROUP BY client_code, MONTH(create_date), YEAR(create_date)
ORDER BY receipt_id;