CODEWITHSUNDEEP
sqlpostgresqlgroup-by
George Nikolov
George Nikolov

Reputation: 173

Postgres SUM and GROUP BY

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

Upvotes: 3

Views: 6289

Answers (3)

alxgrh
alxgrh

Reputation: 26

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)

Upvotes: 0

fqhv
fqhv

Reputation: 1201

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 2

Related Questions