title: Postgres SUM and GROUP BY
body: <p>I'm relatively new to Postgres. I'm using PostgreSQL 9.5.1. </p>
  
  <p>I created simple table to illustrate the issue I'm facing.</p>
  
  <pre><code>CREATE TABLE orders (
      order_id integer NOT NULL,
      order_id_parent integer,
      order_date date,
      item_id integer,
      item_qty integer
  );
  </code></pre>
  
  <p>Here is sample data.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I want to select all orders from 2016-03-24, sum the quantity and group the results by <strong>sub.item_id</strong> to get the qty of each item from different orders. My problem is the GROUP because it forces me to group by <strong>sub.order_id</strong> too and I didn't get the desired result because I want results grouped only by <strong>sub.item_id</strong>. Here is my query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Thanks in advance.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>The answers of @fqhv , @Gordon Linoff and @alxgrh are helpful and showed me different approaches of solving my problem.</p>
  
  <p>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. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Note the "GROUP BY <strong>sub.order_id</strong>"</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The only difference is "GROUP BY <strong>sub.item_id</strong>"
  I'm aware that the <strong>sub.order_id</strong> no longer represents the ID of the row since the results are grouped by <strong>sub.item_id</strong></p>
  
  <p>To get the same result in Postgres I need to remove <strong>sub.order_id</strong> from the select in order to avoid grouping by <strong>sub.order_id</strong> which obviously will not produce desired results. My query in Postgre is:</p>
  
  <pre><code>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
  </code></pre>