none
[Access 2003 + SQL] Subqueries do not follow master query GROUP BY rule RRS feed

  • Question

  • 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...

    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.Table1.agents ;
    
    

     

    How can I fix this, please?

    Regards

     

     

    Friday, November 18, 2011 5:04 PM

All replies

  • At first glance, it looks like you add two times Table1 in the Group By statement, try to change below line:

    GROUP BY Table1.Table1.agents ;

    Into this line:

    GROUP BY Table1.agents;

     

    Also noticed a small typo in the WHERE statement before the Group By statement, you might use single apastrophe:

    WHERE (carS='Sold' Or busS='Sold')

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, November 22, 2011 8:02 AM
    Moderator
  • sorry it's a typo when I wrote this post... any answer?
    Tuesday, November 22, 2011 12:50 PM
  • Can you open the Query, copy all the SQL code, and then paste it here, to make sure you have the exact SQL statement posted?


    Many thanks!

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, November 22, 2011 4:06 PM
    Moderator