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
Best Answer
Original question (dupes within
hstore
value)I think the root cause of your problems is explained in this quote from the manual:
Bold emphasis mine.
You are mistaken in assuming that you could have the same key twice in a single
hstore
value.I can also not reproduce your
count
of2
. I get acount
of1
for the key1614
. Start by not usingcount
as column name. It is a reserved word (but allowed in Postgres).I get (tested with Postgres 9.1.9):
Result:
Updated question (dupes across multiple rows)
To aggregate values you need a subquery (or CTE).
Simplified test case:
Query:
Result: