Finding average of line items from invoices - SQL

Question

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 ?

Answer 1

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;