SQL Server: select the largest order total from multiple customers with multiple orders, and there are multiple items on each order

Question

I am really stuck on a problem and could use a little help. Here is the problem statement:

"Write the query that will show all the customers, the total of all orders, a count of orders made, the average total of each order, average number of items per order (with decimal points), the largest order total and the smallest order total for each customer. Show every customer even if a customer didn't make an order."

These are the tables:

the lovely tables

I've gotten this far, and I'm hung up on the max order total. I was thinking of a subquery for the highest and lowest order totals but I can't make it work.

SELECT
     TC.intCustomerID
    ,TC.strLastName + ',' + ' ' + TC.strFirstName AS strCustomerName
    ,ISNULL(SUM( TCOI.intQuantity * TI.monPrice), 0) AS monOrderTotals
    ,COUNT(DISTINCT TCO.intOrderIndex) AS intNumberOfOrders
    ,ISNULL(SUM(TCOI.intQuantity * TI.monPrice) / COUNT(DISTINCT TCO.intOrderIndex), 0) AS monAverageOrderTotals
    ,(SELECT MAX(TCOI.intQuantity * TI.monPrice)
      FROM TItems AS TI, TCustomerOrderItems AS TCOI
      WHERE TI.intItemID = TCOI.intItemID 
      -- Cross-query join with two columns
      -- AND TC.intCustomerID = TCOI.intCustomerID 
      -- AND TCO.intOrderIndex = TCOI.intOrderIndex 
      ----GROUP BY 
      -- TCOI.intCustomerID 
      --,TCOI.intOrderIndex 
     ) AS monMostExpensiveOrder
FROM
    TCustomers AS TC
LEFT OUTER JOIN 
    TCustomerOrders AS TCO ON (TC.intCustomerID = TCO.intCustomerID)
LEFT OUTER JOIN 
    TCustomerOrderItems AS TCOI ON (TCO.intOrderIndex = TCOI.intOrderIndex)
LEFT OUTER JOIN 
    TItems AS TI ON (TCOI.intItemID = TI.intItemID)
GROUP BY
     TC.intCustomerID
    ,TC.strLastName
    ,TC.strFirstName

Any insight would be greatly appreciated.

Answer 1

For me, using a common table expression goes a long way towards making code easier to read and write when you are working with derived tables (selecting from subqueries).

I think this should cover what you are trying to do, but I was not sure which way you wanted to count average items per order (by number of distinct items or the quantity of items):

with cte as (
  select 
      tc.intCustomerId
    , tc.strFirstName
    , tc.strLastName
    , tcoi.intOrderIndex
    , TotalPrice = isnull(sum(tcoi.intQuantity * ti.monPrice), 0 )
    , ItemCount  = count(*)
    , TotalItemQuantity = sum(tcoi.intQuantity)
  from TCustomers tc
    left join tCustomerOrderItems as tcoi
      on tc.intCustomerId = tcoi.intCustomerId
    left join tItems as ti
      on ti.intItemID = tcoi.intItemID 
)
select 
    intCustomerId
  , Name = isnull(strLastName+', ') + isnull(strFirstName,'')
  , countOrders   = count(intOrderIndex)
  , sumTotalPrice = sum(TotalPrice)
  , minTotalPrice = min(TotalPrice)
  , maxTotalPrice = max(TotalPrice)
  , avgTotalPrice = avg(TotalPrice)
  , avgItemCount  = (sum(ItemCount)+.0)/nullif(count(intOrderIndex),0)
  , avgItemQuant  = (sum(TotalItemQuantity)+.0)/nullif(count(intOrderIndex),0)
from cte
group by 
   intCustomerId
 , strFirstName
 , strLastName

---

To take out the cte part, you would just move the query into the from.

select 
    intCustomerId
  , Name = isnull(strLastName+', ') + isnull(strFirstName,'')
  , countOrders   = count(intOrderIndex)
  , sumTotalPrice = sum(TotalPrice)
  , minTotalPrice = min(TotalPrice)
  , maxTotalPrice = max(TotalPrice)
  , avgTotalPrice = avg(TotalPrice)
  , avgItemCount  = (sum(ItemCount)+.0)/nullif(count(intOrderIndex),0)
  , avgItemQuant  = (sum(TotalItemQuantity)+.0)/nullif(count(intOrderIndex),0)
from (
  select 
      tc.intCustomerId
    , tc.strFirstName
    , tc.strLastName
    , tcoi.intOrderIndex
    , TotalPrice = isnull(sum(tcoi.intQuantity * ti.monPrice), 0 )
    , ItemCount  = count(*)
    , TotalItemQuantity = sum(tcoi.intQuantity)
  from TCustomers tc
    left join tCustomerOrderItems as tcoi
      on tc.intCustomerId = tcoi.intCustomerId
    left join tItems as ti
      on ti.intItemID = tcoi.intItemID 
  ) as cte
group by 
   intCustomerId
 , strFirstName
 , strLastName

Answer 2

You will first need to calculate totals per order and per customer.

I will say that the schema is deficient in not storing order totals, since item price is likely to change, and TCustomerOrders likely includes historical orders. Prefixing tables and column names is also not recommended.

WITH CustomerOrders AS
(
    SELECT 
        oi.intCustomerID as CustomerID,
        oi.intOrderIndex as OrderID,
        SUM(oi.intQuantity * i.monPrice) as SalesAmount,
        COUNT(DISTINCT oi.intItemID) as DistinctItemCount,
        SUM(oi.intQuantity) as ItemCount
    FROM TCustomerOrderItems as oi
    INNER JOIN TItems as i on oi.intItemID = i.intItemID
    GROUP BY oi.intCustomerID, oi.intOrderIndex
),
CustomerSales AS
(
    SELECT
        co.CustomerID,
        SUM(co.SalesAmount) as TotalSalesAmount,
        COUNT(*) as OrderCount,
        AVG(co.SalesAmount) as AvgOrderSalesAmount,
        -- If item count should be distinct SKU's, use DistinctItemCount
        -- Cast to numeric or another non-integer type to get fractional averages
        AVG(CAST(co.ItemCount as numeric(14,4)) as AvgItemCount,
        MIN(co.SalesAmount) as SmallestOrderSalesAmount,
        MAX(co.SalesAmount) as LargestOrderSalesAmount
    FROM CustomerOrders co
    GROUP BY co.CustomerID
)
SELECT
    c.intCustomerID as CustomerID,
    c.strFirstName as CustomerFirstName,
    c.strLastName as CustomerLastName,
    COALESCE(cs.TotalSalesAmount, 0) as TotalSalesAmount,
    COALESCE(cs.OrderCount, 0) as OrderCount,
    COALESCE(cs.AvgOrderSalesAmount, 0) as AvgOrderSalesAmount,
    COALESCE(cs.AvgItemCount, 0) as AvgItemCount,
    COALESCE(cs.SmallestOrderSalesAmount, 0) as SmallestOrderSalesAmount,
    COALESCE(cs.LargestOrderSalesAmount, 0) as LargestOrderSalesAmount
FROM TCustomers c
LEFT OUTER JOIN CustomerSales cs on c.intCustomerID = cs.CustomerID;