SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    Rotterdam
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting with different where conditions

    I have tried searching for this, but it is really difficult to find an answer if you don't know the name for what you are trying to accomplish.

    There are two tables, 'clients' and 'projects'. One client can have multiple projects, so the projects table has a foreign key using clientID. There are two types of projects. The project table has a column projectTypeID, which is either 1 or 2.

    I want to select a client and know how many project they have of type 1, and how many projects of type 2. Can this be done without having multiple separate queries?

    I have looked at subqueries but I'm not sure if it is what I need or how I should use them in this situation.

    Here is my base query:

    Code:
    SELECT
        c.clientID,
        c.clientName,
        count(p.projectID) as projectCount
    FROM
        clients as c
    LEFT JOIN projects as p USING (clientID)
    WHERE
        c.clientID = $clientID
    GROUP BY
        c.clientID
    This gives me the total number of projects for a given client. I could add a condition to the WHERE clause:
    Code:
    WHERE
        c.clientID = $clientID AND
        p.projectTypeID = 1
    but this gives me either the number of projects of type 1 or type 2.

    Could anyone suggest how I can get both the number of type 1 and type 2 projects in one query?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select c.clientID
         , c.clientName
         , sum(case when p.projectTypeID = 1
                    then 1 else 0 end)     as type1projects 
         , sum(case when p.projectTypeID = 2
                    then 1 else 0 end)     as type2projects 
         , count(p.projectID)              as projectCount
      from clients as c
    left outer
      join projects as p 
        on c.clientID = p.clientID
     where c.clientID = $clientID
    group
        by c.clientID
         , c.clientName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    Rotterdam
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Perfect!!!

    This is exactly what I needed! I have never seen that ‘case when’ syntax you used, but I’m going to find out more about it.

    You just made my day

    Thanks for the quick response!


Bookmarks

Posting Permissions

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