gbenroscience gbenroscience - 6 months ago 8
SQL Question

How do I combine these mysql statements

I am currently doing this:

SELECT SS.service_name , SS.id, SH.shortcode FROM smsc_app_db.service SS
INNER JOIN shortcode SH ON SS.confirmation_shortcode = SH.id;

SELECT count(service_id) FROM smsc_app_db.service_status
where smsc_app_db.service_status.service_id = ?;


After running the first select, I get the result in java, then for each row of the ResultSet, I use the SS.ID value to
count the number of subscribers to that service on the service_status table (using the second statement)

But I would like to combine the 2 statements into one.

Is it possible to make the
select count(service_id)


a column in the first select statement?

e.g. if the result of

select count(service_id)....
is stored in
subscriber_base


SELECT SS.service_name , SS.id, SH.shortcode, subscriber_base FROM
smsc_app_db.service SS INNER JOIN shortcode SH ON SS.confirmation_shortcode = SH.id;

vkp vkp
Answer

join the service_status table to the existing query and aggregate to get the counts.

SELECT SS.service_name, SS.id, SH.shortcode, count(*) subscriber_base 
FROM smsc_app_db.service SS 
INNER JOIN shortcode SH ON SS.confirmation_shortcode = SH.id
INNER JOIN smsc_app_db.service_status sstat ON sstat.service_id = SS.id
GROUP BY SS.service_name, SS.id, SH.shortcode
Comments