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... 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
November 18th, 2011 12:26pm

Hi Admin-Dev, Thanks for your post. As we know, subqueries(nested queries) proccessed accordint to current scope condition, you can force it to obey the group rule you specified for top level query scope. If the tables you referred in the subqueries have a mapping relationship with the top level query table, I would suggest you to use inner join keyword to associate them together instead of the nested subqueries. Thanks, Bill Lu Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2011 1:43am

Are you sure you that the query you provided runs without an error message? How can carS be a string and still be accepted as an operand for your SUM operation? If you use subqueries, as you do then SQL Server calculates the SUM for the whole table, and displays the result for each row. If you only want to calculate the SUM, or the COUNT when a specific condition is met you can use a CASE statement. Try the following: , SUM(CASE WHEN condition = 'Yes' THEN Table1.carS ELSE 0 END) AS Car_bought_price , COUNT(CASE WHEN condition = 'Yes' THEN Table1.carB ELSE NULL END) AS Good_condition
November 22nd, 2011 7:41am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics