multiple count & group by?

[homerUK]

hi,
I have 4 tables with many records to one table.
eg:

user_schemas
==========
schema_id
user_id
description

servicelines
==========
serviceline_id
schema_id
name

processes
=========
process_id
schema_id
name

I have got the following SQL working counting the number of servicelines for the one schema record. However, I am not sure how to count other stuff. I need one DB query to get the name of the "user_schema" and a count of all the records in the other tables.

here's what I have

SELECT
us.description,
us.user_id,
count(sl.ud_serviceline_id) AS sl_count
FROM user_schemas us
JOIN servicelines sl
ON us.schema_id = sl.schema_id
WHERE us.user_id = 3
GROUP BY sl.schema_id;

any help is much appreciated!

[Fumigator]

It looks like you're pretty much on track-- what are you stuck on?

[homerUK]

I'm trying to count the number of records in each table with one sql... I have this now but it's returning the number of rows in the process table for each count. eg: the count for ud_serviceline_id should be 9, but it's being returned as 40 which is the process_id count.

SELECT
us.description,
us.user_id,
count(sl.ud_serviceline_id) AS sl_count,
count(pr.process_id) AS pr_count
FROM user_schemas us
LEFT JOIN servicelines sl ON us.schema_id = sl.schema_id
LEFT JOIN processes pr ON pr.schema_id = us.schema_id
WHERE us.user_id = 3
GROUP BY sl.schema_id;

[Fumigator]

Ok I see now the problem. When you do a count(), it's going to return the number of total rows that would normally be returned by that query. With your JOIN query, you are going to get all of the rows from `serviceline` with matching ID, and all of the rows from `process` with matching ID. Selecting a count is, as you have found, going to return the total number of rows in the query result.

There is a way to narrow the count() in a result set using a subset of the result... maybe you could apply it here (I don't know if this will work):

SELECT
us.description,
us.user_id,
count(sl.ud_serviceline_id = us.schema_id OR NULL) AS sl_count,
count(pr.process_id = us.schema_id OR NULL) AS pr_count
FROM user_schemas us
LEFT JOIN servicelines sl ON us.schema_id = sl.schema_id
LEFT JOIN processes pr ON pr.schema_id = us.schema_id
WHERE us.user_id = 3;

BTW I really see no reason for the "GROUP BY" clause since the value of sl.schema_id will be the same for the entire result set.