Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    7

    Exclamation Unanswered: [Access 2003 + SQL] Subqueries do not follow master query GROUP BY rule

    Hi,

    I have a query with 2 subqueries, and no error message is reported, but, my problem is that the 2 subqueries do not follow the GROUP BY rule and show the total instead of by vendor...

    Code:
    SELECT Table1.agents AS Vendor
    , Count(Table1.carS) AS Car_Sold
    , Sum(Table1.carP) AS Car_Price
    , Count(Table1.busS) AS MortBus_Sold
    , Sum(Table1.busP) AS busPRice
    ,  
    (
    SELECT SUM(Table1.carS) 
    FROM Table1
    WHERE (condition='Yes')
    ) 
    AS Car_bought_price, 
    (
    SELECT COUNT(Table1.carB) 
    FROM Table1 
    WHERE  (condition='Yes')
    ) 
    AS Good_condition
    FROM Table1
    WHERE (carS='Sold' Or busS=''Sold' ')
    GROUP BY Table1.agents ;
    Table:
    Code:
     Table1
     Columns:
     agents = John, Bill, Frank...
     carS = Sold, Unsold
     carP = Car selling price
     busS = Sold, unsold
     busP = Bus selling price
     condition = Yes or No
     carB = car buying price
    How can I fix this, please?

    Regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT agents AS Vendor
         , COUNT(carS) AS Car_Sold
         , SUM(carP) AS Car_Price
         , COUNT(busS) AS MortBus_Sold
         , SUM(busP) AS busPRice
         , SUM(IIF(condition='Yes',carS,NULL)) AS Car_bought_price
         , COUNT(IIF(condition='Yes',carB,NULL)) AS Good_condition
      FROM Table1
     WHERE 'Sold' IN (carS,busS)
    GROUP 
        BY agents
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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