I have a carts table with items hstore column. An example entry in this column is:
carts.items row#1 = {
"1614" => {:quantity=>"100", :price_cents=>1655},
"1938" => {:quantity=>"50", :price_cents=>1955},
"1983" => {:quantity=>"100", :price_cents=>2255},
"1322" => {:quantity=>"10", :price_cents=>4455},
"1691" => {:quantity=>"25", :price_cents=>1055},
"1734" => {:quantity=>"20", :price_cents=>1255}
}
carts.items row#2 = {"1614"=>{:quantity=>"50", :price_cents=>1655}}
So my carts table would look like this:
id | items
---------+-------
1 | {"1614"=>{:quantity=>"100", :price_cents=>1655}, "1938" => {:quantity=>"50", :price_cents=>1955},"1983"=>{:quantity=>"100", :price_cents=>2255},"1322"=>{:quantity=>"10", :price_cents=>4455},"1691"=>{:quantity=>"25", :price_cents=>1055},"1734"=>{:quantity=>"20", :price_cents=>1255}}
2 | {"1614"=>{:quantity=>"50", :price_cents=>1655}}
You will notice that there is one duplicate id (1614) in the hash, but its quantity is different.
I want to write a query that will return a table with the item id counts and the total quantity. It should look like this:
item_id | count | total
---------+-------+------
1614 | 2 | 150
1938 | 1 | 50
1983 | 1 | 50
1322 | 1 | 100
Here is the query that I am working with:
SELECT
skeys(carts.items) as item_ids,
COUNT(*) AS count,
svals(carts.items) as items
FROM carts
GROUP BY
skeys(carts.items),
svals(carts.items)
It returns:
item_id | count | total
---------+-------+------
1614 | 1 | {:quantity=>100}
1614 | 1 | {:quantity=>50}
1938 | 1 | {:quantity=>50}
1983 | 1 | {:quantity=>50}
1322 | 1 | {:quantity=>100}
I aslo have tried:
SELECT key, count(*) FROM
(SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY count DESC, key;
Which gives me this:
item_id | count
---------+-------
1614 | 2
1938 | 1
1983 | 1
1322 | 1
1614 | 1 | {:quantity=>500}
?hstore
representation.