0

I am a little stuck on getting a total value for a column using the SUM function. I want to get the total amount of items for an order from my database. The DB order is made up of several line items and I want the total:

Item | quantity | OrderNo
a    | 1        | 1
b    | 2        | 1

Expected Total = 3

I am trying to use a subquery to achieve this but need to get the order number in from the main query I believe.

Here is my query:

Select DISTINCT 

delivery_header.dh_number AS 'OrderNo',

order_header_detail.ohd_delivery_name As 'Receiver Name / Dept',

'1' As '# of Line items', 

'QTY' = (select  SUM(order_line_item.oli_qty_sent)
  
  From delivery_line_item Inner Join
  delivery_header On delivery_header.dh_id = delivery_line_item.dli_dh_id
  Inner Join
  order_line_item On delivery_line_item.dli_oli_id = order_line_item.oli_id
  Inner Join
  variant_detail On variant_detail.vad_id = order_line_item.oli_vad_id
  Inner Join
  order_header On order_header.oh_id = order_line_item.oli_oh_id Inner Join
  stock_location On stock_location.sl_id = order_line_item.oli_sl_id Inner Join
  customer_detail On customer_detail.cd_id = order_header.oh_cd_id Inner Join
  order_header_detail On order_header.oh_id = order_header_detail.ohd_oh_id
  Left Join
  order_header_analysis On order_header.oh_id = order_header_analysis.oha_oh_id
  Left Join
  order_customer_analysis On order_header.oh_id =
    order_customer_analysis.oca_oh_id Left Join
  order_delivery_analysis On order_header.oh_id =
    order_delivery_analysis.oda_oh_id Left Join
  order_line_analysis On order_line_item.oli_id = order_line_analysis.ola_oli_id
  Left Join
  order_line_product_analysis On order_line_item.oli_id =
    order_line_product_analysis.olpa_oli_id Left Join
  order_line_variant_analysis On order_line_item.oli_id =
    order_line_variant_analysis.olva_oli_id Inner Join
  product_detail On product_detail.pd_id = variant_detail.vad_pd_id Inner Join
  delivery_method On delivery_method.dm_id = order_header_detail.ohd_dm_id
  Inner Join
  delivery_method [Delivery Method] On [Delivery Method].dm_id =
    order_header_detail.ohd_dm_id

Inner Join
  delivery_method [Delivery Method1] On [Delivery Method1].dm_id =
    order_header_detail.ohd_dm_id Inner Join
  order_header_total On order_header.oh_id = order_header_total.oht_oh_id
  
  WHERE
delivery_header.dh_number  IN  (
271624
)
  
  ),

Convert(Double Precision, order_header_total.oht_foreign_goods_gross) As 'Value'
 
From delivery_line_item Inner Join
  delivery_header On delivery_header.dh_id = delivery_line_item.dli_dh_id
  Inner Join
  order_line_item On delivery_line_item.dli_oli_id = order_line_item.oli_id
  Inner Join
  variant_detail On variant_detail.vad_id = order_line_item.oli_vad_id
  Inner Join
  order_header On order_header.oh_id = order_line_item.oli_oh_id Inner Join
  stock_location On stock_location.sl_id = order_line_item.oli_sl_id Inner Join
  customer_detail On customer_detail.cd_id = order_header.oh_cd_id Inner Join
  order_header_detail On order_header.oh_id = order_header_detail.ohd_oh_id
  Left Join
  order_header_analysis On order_header.oh_id = order_header_analysis.oha_oh_id
  Left Join
  order_customer_analysis On order_header.oh_id =
    order_customer_analysis.oca_oh_id Left Join
  order_delivery_analysis On order_header.oh_id =
    order_delivery_analysis.oda_oh_id Left Join
  order_line_analysis On order_line_item.oli_id = order_line_analysis.ola_oli_id
  Left Join
  order_line_product_analysis On order_line_item.oli_id =
    order_line_product_analysis.olpa_oli_id Left Join
  order_line_variant_analysis On order_line_item.oli_id =
    order_line_variant_analysis.olva_oli_id Inner Join
  product_detail On product_detail.pd_id = variant_detail.vad_pd_id Inner Join
  delivery_method On delivery_method.dm_id = order_header_detail.ohd_dm_id
  Inner Join
  delivery_method [Delivery Method] On [Delivery Method].dm_id =
    order_header_detail.ohd_dm_id

Inner Join
  delivery_method [Delivery Method1] On [Delivery Method1].dm_id =
    order_header_detail.ohd_dm_id Inner Join
  order_header_total On order_header.oh_id = order_header_total.oht_oh_id

This works, in a manner of speaking, but I need the WHERE clause in my sub query to be taken from the main query so that it is the total for each order, rather than the one specified. If I remove the WHERE it returns a huge number.

If I remove the sub query and try this 'QTY' = delivery_header.dh_number

Then I get an error -

An error occurred: Column 'order_header_detail.ohd_delivery_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there a way to get this:

WHERE delivery_header.dh_number IN ( 271624 )

To contain a dynamic value for each order number pulled out of the DB? I only want 1 line per order number - which is fine at the moment, but each line currently has the same value in QTY which is the totals for the specific order number.

2
  • You need to sum the values from the orderdetails and group by orderid You should be able to do this before joining to all the other tables you should start off with something simpler and build it up step by step – Stephen Morris - Mo64 Apr 13 at 16:00
  • The trouble is I have simplified the query here, it has more columns that this. But I will try and strip it back and start simple, thanks a lot. – J.Pett Apr 13 at 16:08
0

As you affirmed in the comments, the query you currently are trying to work with is very unmaintainable, and is the main issue you're facing while trying to accomplish your goal. Your first priority should be to clean up, refactor, and standardize how the query is written so that you can achieve your goal and future goals when using this query.

In the meantime, until you have time to do the above, you can cheat a little bit (at the cost of potentially being slightly less performant) by using a CTE first to get just the summed up Items per Order (isolated from the rest of your query) and then joining that CTE to your main query like so:

WITH CTE_Orders_Grouped AS
(
     SELECT SUM(quantity) AS ItemsPerOrder, OrderNo
     FROM Orders
     GROUP BY OrderNo
)

SELECT ..., OG.ItemsPerOrder
FROM ...
...
INNER JOIN CTE_Orders_Grouped AS OG -- Replace INNER with LEFT if it's possible for there to be Orders that don't match but you don't want to filter rows from your main query as a result
    ON ... = OG.OrderNo

You'd fill in the ...'s with your main query from above (essentially as is) but with the additional join to the CTE in my example above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.