I have the following data that I need to get a sum of a sum.

The problem with this data is the repetition.

I used the following code to clean it up a bit.

SELECT 
    Item_Number,
    Work_Order_Number,
    SUM(Qty) AS QTY,
    COUNT(Work_Order_ID) AS Count
FROM table
WHERE Item_Number = 'X-1' 
GROUP BY Item_Number, Work_Order_Number

To get the following:

How can I change to code to get the following:

I have tried using this:

SELECT 
    Item_Number,
    Work_Order_Number,
    SUM(Qty) AS QTY,
    COUNT(Work_Order_ID) AS Count
    SUM(SUM(Qty)/COUNT(Work_Order_ID)) AS Total_Quantity
FROM table1
WHERE Item_Number = 'X-1' 
GROUP BY Item_Number

CodePudding user response：

To me it looks like you want the total average quantity.

SELECT 
  Item_Number
, SUM(AverageQuantity) AS Total_Quantity
FROM
(
  SELECT 
    Item_Number
  , Work_Order_Number
  , AVG(Qty) AS AverageQuantity
  FROM table1
  WHERE Item_Number = 'X-1' 
  GROUP BY Item_Number, Work_Order_Number
) q
GROUP BY Item_Number

db<>fiddle here

CodePudding user response：

SELECT Item_Number,
       SUM(QTY) / COUNT(Item_Number) AS Total_Quantity
FROM
  (SELECT 
      Item_Number,
      SUM(Qty) AS QTY
  FROM table
  WHERE Item_Number = 'X-1' 
  GROUP BY Item_Number, Work_Order_Number) T
GROUP BY Item_Number

CodePudding user response：

I understood that you want to get total of a column, if so here is the query.

SELECT SUM(Qty) AS totalQuantiy FROM table1;

If data type is Varchar use COUNT function.