Finding average of line items from invoices

I have this data -

[invoicedetails_key] -- specific line item id number 
[invoicekey] -- invoice number
[invoicedetails_itemname] -- line item name
[invoicedetails_itemqty] -- line item qunatity
[invoicedetails_itemprice] -- line item price
FROM [dbo].[vetfeesinvoicedetails]

invoicedetails_key  invoicekey  invoicedetails_itemname invoicedetails_itemqty
1                   2           consult                 1.00    
2                   2           lab services)(comp)     1.00    
3                   3           urine analysis          1.00    
4                   3           metacam                 0.34
5                   3           mirtazapine             1.00    
6                   4           mirtazapine             1.00    
7                   4           fluid administration    1.00    
8                   5           fluid set up iv         1.00
9                   6           mirtazapine             1.00    
10                  6           hospitalisation         1.00

From this i want to make a temp table of invoice key and invoicedetails key so i can find the average number of invoice line items per invoice.

(invoice details are id's for specific line items and invoice key are which invoice they are from 'invoice 2, invoice 3 etc)

I started with

CREATE TABLE #TempTable (
Invoicekey int,
invoicedetails_key int
)

SELECT *
FROM #TempTable

INSERT INTO #TempTable 
SELECT [invoicedetails_key], [invoicekey]  
FROM [dbo].[vetfeesinvoicedetails]

SELECT *
FROM #TempTable

What is the best way to group 'invoicedetails_key' by 'invoicekey' and then find the average ?

CodePudding user response：

You need to group by invoicekey and get the count of rows, then aggregate again and get the average of all groups.

SELECT
  AVG(id.count * 1.0) AS AverageCount,
  AVG(id.TotalQty * 1.0) AS AverageQty
FROM (
    SELECT
      COUNT(*) AS count,
      SUM(id.invoicedetails_itemqty) AS TotalQty
    FROM dbo.vetfeesinvoicedetails id
    GROUP BY
      id.invoicekey
) id;

CodePudding user response：

You don't need to get data into temp table. You can just group by invoicekey, then use AVG() function in the select statement.

If you mean average of item qty, then it's

SELECT AVG([invoicedetails_itemqty])  
FROM [dbo].[vetfeesinvoicedetails]
GROUP BY [invoicekey]

Not quite sure what you mean by "find the average number of invoice line items per invoice". Can you show me the sample supposed result from this grid above?

CodePudding user response：

Okay so i did it step by step using @Charlieface's idea and managed to come up with this that gave me the correct answer.

SELECT [invoicedetails_key], [invoicekey]  
INTO #TempTable3 
FROM [dbo].[vetfeesinvoicedetails]

SELECT *
FROM #TempTable3

SELECT (cast(invoicekey as int)) as 'Avg'
FROM #TempTable3
GROUP BY invoicekey

SELECT invoicekey, COUNT(invoicedetails_key) AS 'numberoflineitems'
INTO #TempTable4
FROM #TempTable3
GROUP BY invoicekey
ORDER BY invoicekey asc

SELECT AVG(cast(numberoflineitems as decimal(16,4))) as 'Avg'
FROM #TempTable4 ```