Postgres SUM and GROUP BY

Question

I'm relatively new to Postgres. I'm using PostgreSQL 9.5.1.

I created simple table to illustrate the issue I'm facing.

CREATE TABLE orders (
    order_id integer NOT NULL,
    order_id_parent integer,
    order_date date,
    item_id integer,
    item_qty integer
);

Here is sample data.

order_id | order_id_parent |order_date | item_id | item_qty
1           0               2016-03-24      0       0
2           1               NULL            1       2
3           1               NULL            2       2
4           0               2016-03-24      0       0
5           4               NULL            1       5

I want to select all orders from 2016-03-24, sum the quantity and group the results by sub.item_id to get the qty of each item from different orders. My problem is the GROUP because it forces me to group by sub.order_id too and I didn't get the desired result because I want results grouped only by sub.item_id. Here is my query:

SELECT sub.order_id, sub.item_id, SUM(sub.item_qty) AS qty
FROM orders sub
LEFT JOIN orders par ON sub.order_id_parent = par.order_id
WHERE TRUE AND par.order_date = '2016-03-24'
GROUP BY sub.item_id, sub.order_id

Thanks in advance.

UPDATE

The answers of @fqhv , @Gordon Linoff and @alxgrh are helpful and showed me different approaches of solving my problem.

What I'm trying to achieve is one query that outputs different results. The end user needs to have option to group results by sub.order_id or sub.item_id. In first query qty will show the quantity of each row. The second query will group results by sub.item_id and show the sum of all quantities for each sub.item_id . I come from MYSQL world and this is perfectly doable and the result is simplicity of the queries/code. It seems that I need to have two different queries to get the right results. I will post the queries that work in MYSQL the way I expect.

SELECT sub.order_id, sub.item_id, SUM(sub.item_qty) AS qty
FROM orders sub JOIN orders par ON sub.order_id_parent = par.order_id
WHERE par.order_date = '2016-03-24'
GROUP BY sub.order_id

Note the "GROUP BY sub.order_id"

SELECT sub.order_id, sub.item_id, SUM(sub.item_qty) AS qty
FROM orders sub JOIN orders par ON sub.order_id_parent = par.order_id
WHERE par.order_date = '2016-03-24'
GROUP BY sub.item_id

The only difference is "GROUP BY sub.item_id" I'm aware that the sub.order_id no longer represents the ID of the row since the results are grouped by sub.item_id

To get the same result in Postgres I need to remove sub.order_id from the select in order to avoid grouping by sub.order_id which obviously will not produce desired results. My query in Postgre is:

SELECT sub.item_id, SUM(sub.item_qty) AS qty 
FROM orders sub 
  LEFT JOIN orders par ON sub.order_id_parent = par.order_id 
WHERE TRUE AND par.order_date = '2016-03-24' 
GROUP BY sub.item_id

Edit your question and show the information that you do want to get. — Gordon Linoff
– Gordon Linoff, Commented Mar 24, 2016 at 19:30
Do you want top parent order_id with sum all of child, to get total qty for item of order? — Adam Silenko
– Adam Silenko, Commented Mar 24, 2016 at 19:40
Frankly you just using a wrong data scheme. It should be two separated but related tables: orders and order_items. — Abelisto
– Abelisto, Commented Mar 24, 2016 at 20:14
@Adam Silenko - thanks for the lead for "RECURSIVE query" this is new concept for me. Will give it a try. — George Nikolov
– George Nikolov, Commented Mar 25, 2016 at 6:42

Gordon Linoff · Accepted Answer · 2016-03-24 19:33:25Z

2

Is this what you want?

SELECT sub.item_id, SUM(sub.item_qty) AS qty
FROM orders sub JOIN
     orders par
     ON sub.order_id_parent = par.order_id
WHERE par.order_date = '2016-03-24'
GROUP BY sub.item_id;

You don't need a LEFT JOIN because the test for the date requires a match.

Ironically, removing the parent seems like a very small change. The original query is pretty advanced.

answered Mar 24, 2016 at 19:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

fqhv · Accepted Answer · 2016-03-24 19:46:58Z

I think this will give you your desired output. In order to select a value that you are not aggregating or including in your group you need to use a subquery.

SELECT sub.order_id, items.item_id, items.qty
FROM orders sub
INNER JOIN orders par ON sub.order_id_parent = par.order_id
INNER JOIN
(
    SELECT sub.item_id, sum(sub.item_qty) AS qty
    FROM orders sub
    INNER JOIN orders par ON sub.order_id_parent = par.order_id
    WHERE par.order_date = '2016-03-24'
    GROUP BY sub.item_id
) AS items ON sub.item_id = items.item_id
WHERE par.order_date = '2016-03-24'

I changed my mind. I'm not sure what you want. If you want the orders grouped by their parent and item then use this.

SELECT sub.order_id_parent, sub.item_id, sum(sub.item_qty) AS qty
FROM orders sub
INNER JOIN orders par ON sub.order_id_parent = par.order_id
WHERE par.order_date = '2016-03-24'
GROUP BY sub.order_id_parent, sub.item_id

alxgrh · Accepted Answer · 2016-03-24 21:11:26Z

0

If you want to get just quantity of ordered items without relation to specific orders, then don't use sub.order_id after SELECT

test=# SELECT sub.item_id, sum(sub.item_qty) AS qty
FROM orders sub
JOIN orders par ON sub.order_id_parent = par.order_id
WHERE par.order_date = '2016-03-24'
GROUP BY  sub.item_id;
 item_id | qty 
---------+-----
       1 |   7
       2 |   2
(2 rows)

answered Mar 24, 2016 at 21:11

alxgrh

262 bronze badges

Collectives™ on Stack Overflow

Postgres SUM and GROUP BY

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related