PostgreSQL Query to JOIN and SUM

0
votes

I have 2 tables:

  1. orders
  2. orderItems

SUM TOTAL (products price total) of each order s saved on table orders field total. I need to connect these 2 tables and get the sum total and count from the values saved in orders table an example is below

SELECT  
   count(orders.id), sum(orders.total) 
FROM
  orders
INNER JOIN orderItems ON  orderItems.order_no =  orders.order_no 
AND  orders.order_no LIKE 'P%' AND orderItems.pCode IN ('1','2','3','4')

How do I get the sum and count from single query?

2
sqlpostgresqlgroup-by
Can you include sample input/output? - Hambone
order_no count sum P000003013 4 6713.48 P000003023 1 2712.50 P000003025 2 3733.39 P000003313 1 6333.78 P000003313 2 6834.48 The output gives a list , i am looking for the total of the sum and total for count from the list - Codex
Sorry the question is updating as its confusing SELECT count(orders.id) AS CC , sum(orders.total_ordered) AS TT FROM orders WHERE orders.order_no LIKE 'P%' Order table has total and sum added in 2 fields and filtered with order_no starts with P, i need to join with orderItems ON orders.order_no =orderItems.order_no AND orderItems PCode IN ('1','2','3','4') - Codex

2 Answers

0
votes

If you want to use aggregates (e.g. SUM, COUNT) across values (e.g. pCode) then you need to use a GROUP BY clause on the non-aggregated columns:

SELECT  
    orderItems.pCode,
    COUNT(orders.id) AS order_count,
    SUM(orders.total) AS order_total
FROM orders
INNER JOIN orderItems
    ON orderItems.order_no = orders.order_no 
WHERE orders.order_no LIKE 'P%'
AND orderItems.pCode IN ('1','2','3','4')
GROUP BY
    orderItems.pCode

Note how orderItems.pCode is in both the SELECT clause and the GROUP BY clause. If you wanted to list by orders.order_no as well then you would add that column to both clauses too.

0
votes

This is a stab in the dark, but based on your updated comments I think I might know what you are dealing with. It seems like you are doing a sum and count on the order header level from the "orders" table, but by joining to the lines table you are getting multiple records, thus getting a seemingly arbitrary multiplication of both aggregates.

If this is the case, where you only want to sum and count the order header if there is one or more lines that meet your criteria (pCode in 1, 2, 3, 4) then what you want is a semi-join, using the exists clause.

SELECT  
   count(orders.id), sum(orders.total) 
FROM
  orders o
where
  o.order_no like 'P%' and
  exists (
    select null
    from orderItems i
    where
      o.order_no = i.order_no and
      i.pCode in ('1', '2', '3', '4')
  )

What this does is even if you have multiple lines meeting your condition(s), it will still only sum each header once. The syntax takes some getting used to, but the construct itself is very useful and efficient. The alternative would be a subquery "in" list, which on PostgreSQL would not run as efficiently for large datasets.

If that's not what you meant, please edit your question with the sample data and what you expect to see for the final output.