CODEWITHSUNDEEP
sqlpostgresql
PDS
PDS

Reputation: 582

SQL use count or sum for specific result

I was trying to get result with native SQL query as it is presented on below picture, currently im not sure if there is any way to get this result with using only SQL.

I was around this query, but no idea currently further:

 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)

Sample sql data and db table create script:

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'));

Example result

sample result with params

Upvotes: 0

Views: 46

Answers (2)

Mike
Mike

Reputation: 2005

For 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

Upvotes: 1

dave
dave

Reputation: 64657

Assuming mysql, you could just do:

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;

Upvotes: 2

Related Questions