how to improve my query 2

[mimi2]

Hello,
I would like to know how to write the code only once since my two queries are identical except for the age.
Thanks a lot for any hint.

SET @nb_0 =
(SELECT count (*)from
(SELECT distinct client_id
from main m inner join client p
on m.client_id = p.client_id
where (location_id = @location_id) AND (age_id between 1 AND 7)
AND year ( date_done) = @year_of_request
) a
)
SELECT 'age 0-29:',@nb_0

SET @nb_30 =
(
SELECT count (*)from
(SELECT distinct client_id
from main m inner join client p
on m.client_id = p.client_id
where (location_id = @location_id) AND ( age_id between 8 AND 15)
AND year ( date_done) = @year_of_request
) a
)
SELECT 'age 30-90:',@nb_30

 

[checkai]

would this help it at all?

SET @nb_0 =
(SELECT count (distinct client_id)
from main m inner join client p
on m.client_id = p.client_id
where (location_id = @location_id) AND (age_id between 1 AND 7)
AND year ( date_done) = @year_of_request
) a
)
SELECT 'age 0-29:',@nb_0

SET @nb_30 =
(
SELECT count (distinct client_id)
from main m inner join client p
on m.client_id = p.client_id
where (location_id = @location_id) AND ( age_id between 8 AND 15)
AND year ( date_done) = @year_of_request
) a
)
SELECT 'age 30-90:',@nb_30

 

[mimi2]

if possible , i would like to use the following part only once for both queries:

SELECT distinct client_id
from main m inner join client p
on m.client_id = p.client_id
where (location_id = @location_id)
AND year ( date_done) = @year_of_request

then differentiate according to the age_id (a case statement maybe ?)

thanks a lot.

 

[JohnDTampaBay]

Completely untested, but does this do the trick?

SELECT  Age, Count(*) FROM
(SELECT distinct client_id
	, CASE WHEN age_id between 1 AND  7 THEN 'age 0-29:'
		WHEN age_id between 8 AND 15 THEN 'age 30-90:'
	END AS Age
from main m inner join client p
on m.client_id = p.client_id 
where (location_id = @location_id)
AND year ( date_done) = @year_of_request
) a
GROUP BY Age

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[checkai]

select count(distinct client_id),
case when age_id between 1 AND 7 then 'Age 0-29' else
case when age_id between 8 AND 15 then 'Age 30-59' else
'Age 60+' end end
from main m inner join client p
on m.client_id = p.client_id
where (location_id = @location_id)
AND year ( date_done) = @year_of_request

 

[checkai]

I forgot the group by....

 

[mimi2]

that was perfect.
thanks