Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011

    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
    FROM Sales
    WHERE (Status = 'Placed') AND (Status >1) AND (FinalPrice > 1000) )AS s
    WHERE c.CID = s.CID ;

    I'm not to sure what direction to go in.

  2. #2
    Join Date
    Apr 2011

    I hope I am making some progress.

    My code is now at this stage:

    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

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    i'm guessing you don't really need that 3rd table at all
         , c.Name
         , 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))
                               AS AvgPriceOfOrdersOver1000Dollars
      FROM Customer AS c 
      JOIN SalesOrder AS so 
        ON so.CID = c.CID
        BY c.CID
         , c.Name | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2011
    Thank you very much!

    It works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts