How to return correct sum from s subselect?-CodePudding

I have a subselect which is part of a bigger select. I want to SUM all weights for one order and display that sum, but constantly get errors or multiple returned values:

SELECT "Invoice Date", "Invoice Number", "Invoice Currency Code",    
(select sum(items.GEWICHTF) from consignment left join items on items.consid=consignment.id group by consignment.orderNo)) "Weight", 
    
FROM order
LEFT JOIN partner ON order.customer=partner.nr
LEFT JOIN consignment ON order.nr=consignment.orderno
LEFT JOIN items ON items.consid=consignment.id
LEFT JOIN transportorder ON consignment.consid=transportorder.consid

WHERE partner.nr=26601102;

How can I do that?

CodePudding user response：

As you said that you don't know what a correlated subquery is, then (read comments within code):

SELECT "Invoice Date",
       "Invoice Number",
       "Invoice Currency Code",
       --
       -- this is a correlated subquery; it puts ITEMS table to relation with ...
       (SELECT SUM (items.gewichtf)
          FROM items 
         WHERE items.consid = consignment.id) "Weight"
  FROM t_order
       LEFT JOIN partner ON order.customer = partner.nr
       LEFT JOIN consignment ON order.nr = consignment.orderno  --> ... the CONSIGNMENT table
       LEFT JOIN items ON items.consid = consignment.id
       LEFT JOIN transportorder ON consignment.consid = transportorder.consid
 WHERE partner.nr = 26601102;

I understand, you posted just a small piece of code you really have so this may, or may not work.

To comment your comment about what Alex commented:

It would be impossible to group the whole query.

Impossible? On the other hand, it would be possible - it would just require some more typing, to specify all non-aggregated columns into the group by clause.

Would it work properly? I have no idea, it depends on what you want.

Option which would work is to create a function that calculates the result and then call that function from your query, directly:

CREATE OR REPLACE FUNCTION f_weight (par_id IN items.consid%TYPE)
   RETURN NUMBER
IS
   retval  NUMBER;
BEGIN
   SELECT SUM (i.gewichtf)
     INTO retval
     FROM items i
    WHERE i.consid = par_id;

   RETURN retval;
END;
/

SELECT "Invoice Date",
       "Invoice Number",
       "Invoice Currency Code",
       --
       f_weight (consignemnt.id) "Weight"               --> here
  FROM t_order
       LEFT JOIN partner ON order.customer = partner.nr

Doing so, you wouldn't need a GROUP BY at all (unless there are other aggregates there).

Drawback? Context switching; might (and will) slow things down. For small data set, you won't notice the different. For large data set, you most probably will.

Finally, just a comment: this is Oracle, and we usually hate enclosing anything into double quotes and put spaces in between words. Let the front end care about labels. I'd suggest you to

select invoice_date, invoice_currency_code, weight from ...

and then (as I said) use any label you want in front end.

CodePudding user response：

If all the rows are in your outer query (without duplicates) then you you can use the SUM analytic function:

SELECT "Invoice Date",
       "Invoice Number",
       "Invoice Currency Code",
       SUM(items.GEWICHTF) OVER (PARTITION BY consignment.orderNo) AS "Weight"
FROM   order
       LEFT JOIN partner ON order.customer=partner.nr
       LEFT JOIN consignment ON order.nr=consignment.orderno
       LEFT JOIN items ON items.consid=consignment.id
       LEFT JOIN transportorder ON consignment.consid=transportorder.consid
WHERE  partner.nr=26601102;