Postgres nested hstore select query
Original question (dupes within hstore value)

I think the root cause of your problems is explained in this quote from the manual:


  Each key in an hstore is unique. If you declare an hstore with
  duplicate keys, only one will be stored in the hstore and there is no
  guarantee as to which will be kept.


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 of 2. I get a count of 1 for the key 1614. Start by not using count as column name. It is a reserved word (but allowed in Postgres).

I get (tested with Postgres 9.1.9):

WITH carts AS (
   SELECT '"1614"=&gt;{:quantity=&gt;"100"}, "1938"=&gt;{:quantity=&gt;"50"}, "1614"=&gt;{:quantity=&gt;"50"}, "1983"=&gt;{:quantity=&gt;"100"}, "1322"=&gt;{:quantity=&gt;"10"}, "1691"=&gt;{:quantity=&gt;"25"}, "1614"=&gt;{:quantity=&gt;"77"}, "1734"=&gt;{:quantity=&gt;"20"}'::hstore items
    )
SELECT key, count(*) AS ct FROM
  (SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY ct DESC, key;


Result:

 key  | ct
------+----
 1322 |  1
 1614 |  1
 1691 |  1
 1734 |  1
 1938 |  1
 1983 |  1


Updated question (dupes across multiple rows)

To aggregate values you need a subquery (or CTE).

Simplified test case:

CREATE TEMP TABLE carts(c_id serial, items hstore); 
INSERT INTO carts(items) VALUES
   ('"1614"=&gt;"100", "1938"=&gt;"50", "1983"=&gt;"100", "1322"=&gt;"10", "1691"=&gt;"25", "1734"=&gt;"20"')
  ,('"1614"=&gt;"50"');


Query:

SELECT item_ids, count(*) AS ct, sum(items) AS sum_items
FROM (
   SELECT (each(items)).key AS item_ids
         ,(each(items)).value::int AS items  -- assuming values can be cast to int
   FROM   carts
   ) sub
GROUP  BY 1           -- positional parameter is just a notational convenience
ORDER  BY 2 DESC;


Result:

 item_ids | ct | sum_items
----------+----+-----------
 1614     |  2 |       150
 1734     |  1 |        20
 1691     |  1 |        25
 1983     |  1 |       100
 1938     |  1 |        50
 1322     |  1 |        10