CODEWITHSUNDEEP
sqlpostgresqlsubqueryaggregate
Ahamed Moosa
Ahamed Moosa

Reputation: 1445

subquery calculation with postgreSQL

I have arrived at a query result by writing the below query 

   SELECT a.indent_date as "Invoice_Date", a.indent_no as "Invoice_No",
          d.product_description as "Product Description",
          e.name as "Product_Category",
          c.name as "Customer",
          a.amount as "Invoice_Amount",
     FROM indents a
     JOIN parties b on (a.supplier_id = b.id)
     JOIN parties c on (a.buyer_id = c.id)
     JOIN indent_items d on (a.id = d.indent_id)
     JOIN product_categories e on (a.product_category_id = e.id)
    WHERE c.name <> 'SOFEENE ENTERPRISES' 
      And b.name = 'SOFEENE ENTERPRISES' 
      And indent_date >= '2017-10-01 00:00:00' 
      And indent_date <= '2017-12-31 00:00:00'
 Order By indent_date ASC

And the table looks like below 

Invoice_Date    Invoice_No  Product Category    Customer    Invoice_Amount 
    2/10/2017          1          Spandex       A                     1000  
    3/10/2017          2          Spandex       B                     2000  
    3/10/2017          2          Spandex       B                     1000  
    3/10/2017          2          Spandex       B                     2000  
    3/10/2017          2          Spandex       B                     1000  
    4/10/2017          3          Spandex       B                     2000  
    4/10/2017          3          Spandex       B                     1000  
    4/10/2017          3          Spandex       B                     2000  
    4/10/2017          3          Spandex       B                     1000  
    4/10/2017          3          Spandex       B                     2000

Now I want to make another query from this table and my end table should look like.

Invoice_Date    Invoice_No  Product Category    Customer    Invoice_Amount
2/10/2017              1            Spandex           A              1000
3/10/2017              2            Spandex           B              6000
4/10/2017              3            Spandex           B              8000

Actually I want to group by Invoice_no and add the Invoice Amount.

I tried subqueries, but somehow it's not working. I am working with Postgres SQL.

Upvotes: 0

Views: 93

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

You need to add grouping and sum your invoice amount:

SELECT a.indent_date as "Invoice_Date", a.indent_no as "Invoice_No",
       d.product_description as "Product Description",
       e.name as "Product_Category",
       c.name as "Customer",
       sum(a.amount) as "Invoice_Amount" -- put a SUM() around that
       FROM indents a
JOIN parties b on (a.supplier_id = b.id)
JOIN parties c on (a.buyer_id = c.id)
JOIN indent_items d on (a.id = d.indent_id)
JOIN product_categories e on (a.product_category_id = e.id)
WHERE c.name <> 'SOFEENE ENTERPRISES' and b.name = 'SOFEENE ENTERPRISES' 
      and indent_date >= '2017-10-01 00:00:00' 
      and indent_date <= '2017-12-31 00:00:00'
GROUP BY 1,2,3,4,5 -- these qualify for columns in order from SELECT list
ORDER BY indent_date

If your columns order in SELECT change, grouping will also change, so this might be a handy shortcut but you need to be careful about adjusting it always when you mess around your columns display. If you wish to have it the usual way, just:

GROUP BY a.indent_date, a.indent_no, d.product_description, e.name, c.name

Upvotes: 1

Related Questions