Unanswered: table-valued subquery within from clause
Hello me again...
I'm trying to list customers who have more than one order listed as "placed" and establish how many of those orders are over $1000. I also need to show the average price of the orders over $1000... but I'm just not grasping the code. I get this far and it errors... but I dont know why...
SELECT Name, OrdersLarger1000
FROM Customer AS c, (SELECT CID, COUNT(Status) AS OrdersLarger1000
WHERE (Status = 'Placed') AND (Status >1) AND (FinalPrice > 1000) )AS s
WHERE c.CID = s.CID ;
SELECT c.CID, c.Name, COUNT(Status) AS NbrOrdersOver1000Dollars, Avg(FinalPrice ) AS AvgPriceOfOrdersOver1000Dollars
FROM Customer AS c INNER JOIN (SalesOrder AS so INNER JOIN SalesOrderProduct AS sop ON so.SOID = sop.SOID) ON c.CID = so.CID
WHERE so.FinalPrice > 1000 AND so.Status = 'Placed'
GROUP BY c.CID, c.Name;
But... I'm not to sure if I have the count status correct. It counts the total status records which is 5. I only need it to count the status listed at "placed" which is 4 and then the placed status's over $1000 which is 2 records.
Help would be greatly appreciated. Thank you in advance
i'm guessing you don't really need that 3rd table at all
, COUNT(IIF(so.Status = 'Placed',1,NULL)) AS NbrOrdersPlaced
, COUNT(IIF(so.Status = 'Placed'
AND so.FinalPrice > 1000,1,NULL)) AS NbrOrdersOver1000Dollars
, AVG(IIF(so.Status = 'Placed'
AND so.FinalPrice > 1000,FinalPrice,NULL))
FROM Customer AS c
JOIN SalesOrder AS so
ON so.CID = c.CID