CODEWITHSUNDEEP
sqlpostgresql
StillLearningToCode
StillLearningToCode

Reputation: 2461

summing dynamic rows using over partition by postgres

on postgres 9.2

| payer| effective_status | 1     |    2   | 3   | 4+
+------+ -----------------+-------+--------+-----+-----
|  p1  | foo              |     8 |   6000 |    4|  1
|  p1  | bar              |    10 |   5200 |    9|  2
|  p1  | baz              |    11 |   5200 |   11|  2
|  p1  | zip              |     9 |   4500 |   14|  4
|  p1  | zap              |     7 |   4200 |   45|  5
|  p1  | status_n         |     2 |   3900 |   71|  1

suppose the above query output (minus the ??s). i am trying to sum columns 1, 2, 3, and 4+ by payer and effective status. so for p1 there would be a column total including all effective_statuses, and then p2 would have a group total. 

    | p1   | effective_status | 1     |    2   | 3   | 4+| 1 total | 2 total|3 total| 4+ total
    +------+ -----------------+-------+--------+-----+---+---------+--------+-------+----------
    |      | foo              |     8 |   6000 |    4|  1|    94   |  6230  |  154  | 15
    |      | bar              |    10 |   5200 |    9|  2|    94   |  6230  |  154  | 15
    |      | baz              |    11 |   5200 |   11|  2|    94   |  6230  |  154  | 15
    |      | zip              |     9 |   4500 |   14|  4|    94   |  6230  |  154  | 15
    |      | zap              |     7 |   4200 |   45|  5|    94   |  6230  |  154  | 15
    |      | status_n         |     2 |   3900 |   71|  1|    94   |  6230  |  154  | 15

how would i calculate the ??s? my I ave tried:

payer
,effective_status
,status_check1
,SUM(status_check1) OVER (PARTITION BY payer) AS status_check1_total
,status_check2
,SUM(status_check2) OVER (PARTITION BY payer) AS status_check2_total
,status_check3
,SUM(status_check3) OVER (PARTITION BY payer) AS status_check3_total
,status_check4
,SUM(status_check4) OVER (PARTITION BY payer) AS status_check4_total

which seems to work, most of the time. on occasion there are wrong totals. is this the correct approach?

Upvotes: 0

Views: 142

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I'm not sure why you are using window functions. This would appear to be union all:

select payer, effective_status, status_check1, status_check2, status_check3, status_check4
from t
union all
select payer, null, sum(status_check1), sum(status_check2), sum(status_check3), sum(status_check4)
order by payer, effective_status nulls last;

Postgres 9.5 supports grouping sets which simplifies such logic.

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

If I understand correctly, you can use UNION ALL to combine total result set and your original table. then use order by by the grp order.

CREATE TABLE T(
  payer varchar(50),
  effective_status varchar(50),
  status_check1 int,
  status_check2 int,
  status_check3 int,  
  status_check4 int
);


INSERT INTO T VALUES ('p1', 'foo',8 ,6000,4,1);
INSERT INTO T VALUES ('p1', 'bar',10,5200,9,2);
INSERT INTO T VALUES ('p1', 'baz',11,5200,11,2);
INSERT INTO T VALUES ('p1', 'zip',9 ,4500,14,4);
INSERT INTO T VALUES ('p1', 'zap',7 ,4200,45,5);
INSERT INTO T VALUES ('p1', 'status_n',2 ,3900,71,1);
INSERT INTO T VALUES ('p2', 'foo',5 ,3500,12,2);
INSERT INTO T VALUES ('p2', 'zip',1 ,5000,1,1);

Query 1:

SELECT * 
FROM (
  SELECT t1.payer
    ,effective_status
    ,status_check1
    ,status_check2
    ,status_check3
    ,status_check4
    ,1 grp
  FROM T t1 
  UNION ALL
  SELECT payer,
         '',
         SUM(status_check1),
         SUM(status_check2),
         SUM(status_check3),
         SUM(status_check4),
         2
  FROM T
  GROUP BY payer 
) t1
ORDER BY payer,grp

Results:

| payer | effective_status | status_check1 | status_check2 | status_check3 | status_check4 | grp |
|-------|------------------|---------------|---------------|---------------|---------------|-----|
|    p1 |              foo |             8 |          6000 |             4 |             1 |   1 |
|    p1 |              bar |            10 |          5200 |             9 |             2 |   1 |
|    p1 |              baz |            11 |          5200 |            11 |             2 |   1 |
|    p1 |              zip |             9 |          4500 |            14 |             4 |   1 |
|    p1 |              zap |             7 |          4200 |            45 |             5 |   1 |
|    p1 |         status_n |             2 |          3900 |            71 |             1 |   1 |
|    p1 |                  |            47 |         29000 |           154 |            15 |   2 |
|    p2 |              foo |             5 |          3500 |            12 |             2 |   1 |
|    p2 |              zip |             1 |          5000 |             1 |             1 |   1 |
|    p2 |                  |             6 |          8500 |            13 |             3 |   2 |

Upvotes: 1

Sheruan Bashar
Sheruan Bashar

Reputation: 516

Actually, I didn't get clearly what you are trying to do, but if you want to have result grouped by payer and effective_status it possibly would look like this

select 
   payer as p, 
   effective_status as es,
   (sum(col1) + sum(col2) + sum(col3) + sum(col4)) as sum
from table_name
group by p, es

So, hope it will help you

Upvotes: 0

Related Questions