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.
Unnest the hstore column:
select key, value::dec
from mytry, each(combined_paym)
where paym::int = 6
key | value
-----+-------
0 | 4
2 | 4
3 | 4
0 | 2
1 | 4
3 | 4
4 | 4.15
(7 rows)
and use it in union:
select paym, sum(price)
from (
select paym, price
from mytry
where paym::int < 6
union all
select key, value::dec
from mytry, each(combined_paym)
where paym::int = 6
) s
group by 1
order by 1;
paym | sum
------+-------
0 | 39.70
1 | 21.00
2 | 4
3 | 20.16
4 | 17.15
(5 rows)