Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post

    multiple count & group by?

    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

    Code:
    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!

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    It looks like you're pretty much on track-- what are you stuck on?

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    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.

    Code:
    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;

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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):

    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •