Select Query With Aggregate - Need Help 1

[menkes]

OK, I cannot figure this one out.

In one table, tblAPPLICATION, I need to retrieve one or more rows for a specific client. The key for each row is the application code, which is a one byte alpha character.

From another table, tblESTIMATE, I need to retrieve the SUM of all estimates for each application code retrieved in the above query.

I can do these as separate queries, but need to do it as a single query. Here are the 2 queries:

SELECT a.*
FROM tblAPPLICATION
WHERE client_id = aKnownValue

Repeat this query for each result set from above query:
SELECT SUM(estimate_amt) AS estimate_total
FROM tblESTIMATE
WHERE proj_id = aKnownValue
AND appl_code = applCodeFromAboveQuery

Any ideas on how to merge these???

 

[mrdenny]

A join would work for you.

select tblApplication.* /*This would need to actually be a list of columns needed, not an '*'*/,
    sum(tblEstimate.estimage_amt)
from tblApplication
join tblEstimate on tblApplication.appl_code = tblEstimate.appl_code
   and tblEstimage.proj_id = aKnownValue
where client_ID = aKnownValue
group by /*The list of columns from above from tblApplication*/

Denny

--Anything is possible. All it takes is a little research. (Me)