Hi All,
I am running a query below:
Select A.location (select sum(col1) from table1 where condition), (select sum(col2) from table2 where conditions given) from table1 left join table 2 on table1.col6=table2.col7 group by location.
The problem is the sub queries are giving constant results for each location.i wannt the subquery results to change as per location.Please suggest.
Select A.location (select sum(col1) from table1 t1 where condition AND t1.location = A.location), (select sum(col2) from table2 t2 where conditions given AND t2.location = A.location) from table1 left join table 2 on table1.col6=table2.col7 group by A.location
Scott,
The problem is elaborated as below :I have one table A with columns report_date,location ,segment ,and c_number and another table B with columns report date ,location and d_no. I have to create a table which contains 2 column location and below calculation Wos= total c_no with condition segment= dim and report date as latest date to be fetched from table A / total d_no for last 6 weeks from present report date to be fetched from table B.
I joined the two tables and use subquery select but the result is giving me constant value for all locations.Could you please suggest on solutions and how to approach?
Thank you for the help
Yes, as I showed above, add a condition to the WHERE clause on the subqueries to check for the location name matching the outer query.
Select A.location (select sum(col1) from table1 t1 where conditions given AND t1.location = A.location), (select sum(col2) from table2 t2 where conditions given AND t2.location = A.location) from table1 left join table 2 on table1.col6=table2.col7 group by A.location
Thank you Scott.you truly are a genius.