Find Average Sales Amount Per Customer T-SQL-CodePudding

I am following a tutorial for T-SQL. My task is to find the total amount per customer and the avarage amount per customer. My solution is the following -

SELECT o.custid, SUM(d.unitprice*d.qty) AS TotalPerCustID, AVG(d.unitprice*d.qty) as AvarageSalesAmount
FROM Sales.Orders as o
INNER JOIN Sales.OrderDetails as d ON
o.orderid=d.orderid
GROUP BY o.custid
ORDER BY o.custid

The result that I get is correct for exactly 12 orders per custid = '1' -

custid	TotalPerCustID	AverageSalesAmount
1	4596.20	383.0166

However, the tutorial is suggesting that the right solution is the following -

SELECT
    c.custid,
    SUM(c.totalsalesamountperorder) AS totalsalesamount,
    AVG(c.totalsalesamountperorder) AS avgsalesamount
FROM
(
    SELECT 
        o.custid, o.orderid, SUM(d.unitprice * d.qty) AS totalsalesamountperorder
    FROM Sales.Orders AS o 
    INNER JOIN Sales.OrderDetails d ON d.orderid = o.orderid
    GROUP BY o.custid, o.orderid
) AS c
GROUP BY c.custid

The result of this solution is, according to my opiniont wrong -

custid	totalsalesamount	avgsalesamount
1	4596.20	766.0333

Please advise if there is a difference in the two solutions and which one is the correct one. Thank you.

CodePudding user response：

Their query looks correct. Issue is your average(which is sum/count) is wrong because you are using the wrong count. You are using the count of order details, not count of orders. They work around this by first rolling up by orderID, then applying their AVG aggregate.

If you want do it without the sub-select, I'd change your query to calculate the average like so:

SELECT SUM(d.UnitPrice*d.Qty)/COUNT(DISTINCT o.OrderID) AS AverageSalesAmount