PostgreSQL, SUM and GROUP from numeric column and hstore

Question

I would kindly ask if someone could make me a query which may SUM up values from numeric column and from hstore column. This is obviously too much for my SQL abbilities.

A table:

DROP TABLE IF EXISTS mytry; 
CREATE TABLE IF NOT EXISTS mytry 
   (mybill int, price numeric, paym text, combined_paym hstore); 
INSERT INTO mytry (mybill, price, paym, combined_paym)
VALUES (10,  10.14, '0', ''),
       (11,  23.56, '0', ''),
       (12,  12.16, '3', ''),
       (13,  12.00, '6', '"0"=>"4","3"=>"4","2"=>"4"'),
       (14,  14.15, '6', '"0"=>"2","1"=>"4","3"=>"4","4"=>"4.15"'),
       (15,  13.00, '1', ''),
       (16,   9.00, '4', ''),
       (17,   4.00, '4', ''),
       (18,   4.00, '1', '');

Here is a list of bills, price and payment method for each bill.
Some bills (here 13 and 14) could have combined payment. Payment methods are enumerated from 0 to 5 which describes specific payment method.
For this I make this query:

SELECT paym, SUM(price) FROM mytry WHERE paym::int<6 GROUP BY paym ORDER BY paym;

This sums prices for payment methods 0-5. 6 is not payment method but a flag which means that we should here consider payment methods and prices from hstore 'combined_paym'. This is what I don't know how to solve. To sum payment methods and prices from 'combined paym' with ones from 'paym' and 'price'.

This query gives result:

"0";33.70
"1";17.00
"3";12.16
"4";13.00

But result is incorrect because here are not summed data from bill's 13 and 14.
Real result should be:

"0";39.70
"1";21.00
"2";4.00
"3";20.16
"4";17.15

Please if someone can make me proper query which would give this last result from given data.

klin · Accepted Answer · 2024-08-07 12:01:36Z

2

Unnest the hstore column wih the each() function:

select key, value::numeric(10,2)
from mytry
cross join each(combined_paym)
where paym::int = 6

and use it in union:

select paym, sum(price)
from (     
    select paym, price
    from mytry
    where paym::int < 6
    union all
    select key, value::numeric(10,2)
    from mytry
    cross join each(combined_paym)
    where paym::int = 6
    ) s
group by 1
order by 1;

Test it in db<>fiddle.

edited Aug 7, 2024 at 12:01

answered Oct 31, 2016 at 19:06

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wine Too Over a year ago

I thought about something like this but was unable to put it together. Great! This look's like it gives proper result. Also, I never before uses 'each' instead of -> for unnesting hstore elements. Thank you for solution and workable example.

Collectives™ on Stack Overflow

PostgreSQL, SUM and GROUP from numeric column and hstore

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related