Selecting 2 counts in 1 query.

Question

Trying to get two different counts in one query. To do this I am selecting from a table and need to join another one. Check out the (not working) queries below. These were different ways I was trying to do it - unsuccessfully.

Is this possible, and if so can you offer assistance getting a working query?

SELECT 
 count( tasks_assigned.task_id ) AS task_assigned_count,
 count( tasks_created.task_id ) AS task_created_count,
FROM projects
LEFT JOIN tasks AS tasks_assigned 
 ON tasks_assigned.project_id = projects.project_id
 AND tasks_assigned.assigned_user_id = 1
LEFT JOIN tasks AS tasks_created 
 ON tasks_created.project_id = projects.project_id
 AND tasks_created.created_user_id = 1
GROUP BY tasks_assigned.project_id, tasks_created.project_id


SELECT 
 projects.*
 , (SELECT count(task_id) as task_assigned_count FROM tasks as task_assigned WHERE task_assigned.project_id = projects.project_id AND assigned_user_id = 1 GROUP BY task_id) as task_assigned_count
 , (SELECT count(task_id) as task_created_count FROM tasks as task_created WHERE task_created.project_id = projects.project_id AND assigned_user_id = 1 GROUP BY task_id) as task_assigned_count
FROM projects

Answer 1

SELECT 
projects.*
, (SELECT count(*) FROM tasks WHERE project_id = projects.project_id AND assigned_user_id = 1) as task_assigned_count,
, (SELECT count(*) FROM tasks WHERE project_id = projects.project_id AND created_user_id = 1) as task_created_count
FROM projects

Answer 2

SELECT (SELECT COUNT(task_id) FROM tasks_assigned) AS task_assigned_count, (SELECT COUNT(task_id) FROM tasks_created) AS task_created_count

Answer 3

Get rid of the grouping in the subqueries:

SELECT 
      projects.*,
     (SELECT count(task_id) FROM tasks
      WHERE task_assigned.project_id = projects.project_id
      AND assigned_user_id = 1) as task_assigned_count,
...
FROM projects

Answer 4

Not 100% if this is what you are trying to do, but it looks like you should be grouping by project id. You could do:

SELECT projects.project_id, assigned_count.count, created_count.count
FROM projects
LEFT JOIN (SELECT project_id,
                  count(*) AS count,
           FROM projects
           INNER JOIN tasks A 
                  ON tasks.project_id = projects.project_id
           WHERE tasks.assigned_user_id = 1
           GROUP BY projects.project_id) assigned_count
 ON assigned_count.project_id = projects.project_id
LEFT JOIN (SELECT project_id,
                  count(*) AS count,
           FROM projects
           INNER JOIN tasks A 
                  ON tasks.project_id = projects.project_id
           WHERE tasks.created_user_id = 1
           GROUP BY projects.project_id) created_count
 ON created_count.project_id = projects.project_id

or:

SELECT  projects.*,
        SUM( IF(tasks.assigned_user_id = 1,       1,0) ) AS task_assigned_count,
        SUM( IF(tasks_created.created_user_id = 1,1,0) ) AS task_created_count
FROM projects
LEFT JOIN tasks A 
        ON tasks.project_id = projects.project_id
GROUP BY projects.project_id

Answer 5

I would do this in two queries:

SELECT p.project_id, COUNT( a.task_id ) AS task_assigned_count
FROM projects p
LEFT JOIN tasks AS a ON a.project_id = p.project_id
  AND a.assigned_user_id = 1
GROUP BY p.project_id;

SELECT p.project_id, COUNT( c.task_id ) AS task_created_count
FROM projects p
LEFT JOIN tasks AS c ON c.project_id = p.project_id
  AND a.created_user_id = 1
GROUP BY p.project_id;

Otherwise you've created a Cartesian product between the two instances of tasks. You could also use DISTINCT but this is only compensating for the Cartesian product, not avoiding it:

SELECT p.project_id, COUNT( DISTINCT a.task_id ) AS task_assigned_count,
    COUNT( DISTINCT c.task_id ) AS task_created_count
FROM projects p
LEFT JOIN tasks AS a ON a.project_id = p.project_id
  AND a.assigned_user_id = 1
LEFT JOIN tasks AS c ON c.project_id = p.project_id
  AND a.created_user_id = 1
GROUP BY p.project_id;