Sql-server – SQL sql subquery with SUM for totals

sql serversubquerysum

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.

Best Answer

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.

Related Solutions

Mysql – To find out the sum total of GRNQty and GRNValue of particular item from column 1

To fix the error: You exposed Qty AS GRNQty; use the latter outside.

If there is a question about Q1, I missed it.

I recommend using some extra parens when doing UNION:

( SELECT ... )
UNION ALL
( SELECT ... )

Without these parens, you might add a clause (eg LIMIT) and have it mysteriously apply to the second SELECT instead of the UNION.

MySQL – Multi-Table Sum of Cost Breakdown by Pricing Category

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query.

Doing a simple row-based group of your sample dataset:

MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)

You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*), 
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;

With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)

You can use the same approach to get your date-based rollups.

Best Answer

Related Solutions

Mysql – To find out the sum total of GRNQty and GRNValue of particular item from column 1

MySQL – Multi-Table Sum of Cost Breakdown by Pricing Category

Related Question