Count field, joins, multiple selects

Question

I have three tables. Estimates, Estimate_versions, and customers.

Here is some SQL

SELECT estimates.id,
estimates.estimate_number, 
estimates.description,
estimates.meeting_date,
estimates.job_date,
estimates.status,
estimates.price

FROM
(estimates)
LEFT OUTER JOIN estimate_versions estimate_versions ON estimate_versions.estimate_id =     estimates.id
LEFT OUTER JOIN customers customers ON customers.id = estimates.customer_id
WHERE customers.key = 'JsB4ND90bn'

This works -- What I want to do is add a field at the very end of the table. Essentially, I want it to 'count', the number of records in estimate_versions, that contain the current rows, estimate.id, here is some non-working pseudocode of what I basically want in the final field

count(where estimate_versions.estimate_id = estimates.id)

When I try and do a few different ways of achieving this, I usually get ONE row of data, with one number in it. Instead of lets say, 3 records, and the count field containing the appropriate number.

Looking forward to receiving some much needed aid, my SQL skills are weak.

Answer 1

I think this is what you are looking for.

SELECT estimates.id,
estimates.estimate_number, 
estimates.description,
estimates.meeting_date,
estimates.job_date,
estimates.status,
estimates.price,
count(estimate_versions.estimate_id)
FROM
(estimates)
LEFT OUTER JOIN estimate_versions estimate_versions ON estimates.id = estimate_versions.estimate_id
LEFT OUTER JOIN customers customers ON estimates.customer_id = customers.id
WHERE customers.key = 'JsB4ND90bn'
group by
estimates.id,
estimates.estimate_number, 
estimates.description,
estimates.meeting_date,
estimates.job_date,
estimates.status,
estimates.price