Search code examples
mysqlsqljoinselectsubquery

To count and return multiple row in single sql query and print result as table

I have a doubt in related to sql query. I will explain it down

**Table People**
| S.No| name|task|
| ----| ----|----|
| 1   | n1  |t1  |
| 2   | n2  |t2  |
| 3   | n3  |t1  |
| 4   | n4  |t1
| 5   | n5  |t2  |
**Table Tasks**
| S.No| taskid|taskname|
| ----| ----|----|
| 1   | t1  |task1|
| 2   | t2  |task2|
| 3   | t3  |task3|
| 4   | t4  |task4|
| 5   | t5  |task5|

I need to get the count of how many people a task is assigned to like this

**Expected result**
|task |count|
|-----|-----|
|task1| 3   |
|task2| 2   |
|task3| 0   |

I tried a query like this below but It doesn't work

SELECT COUNT(TASK) FROM PEOPLE WHERE TASK IN (SELECT TASKID IN TASKS)

The above query returns overall count of all tasks . What I need to modify in the query to get expected result

Solution

  • It's more like:

    SELECT t.taskname as task, COUNT(p.task) as `count`
FROM 
  task t
  LEFT JOIN people p  ON p.task = t.taskid
GROUP BY
  t.taskname

    The left join from tasks (gets all task record) to people (might not get any people record, so for eg task3, p.task is null) will certainly contain all tasks, but not all people.. For those tasks that have multiple people, the taskname will repeat multiple times. When it is grouped, it becomes a single one, and we can count the group corrently

    It is important to count on p.task, rather than COUNT(*) because COUNT(*) will see a row like:

    name,  p.task
task3, null

    ..and if we count(*)'d it would count as 1, because it is a row.. but we want to count p.task because NULLs are never counted, so we get a count of 0 for that task

    I'd have called people.task as people.taskid, personally

    I'm not really sure what you mean by "print result as table" - that's a job for the front end really, but if you mean you want to format the output, then you'll need to look at something like CONCAT(...) or CONCAT_WS(...) which will allow you to produce formatted text as the output

    If your task names are not unique then you can, as @stickybit says in the comments, put t.taskid in the group by too (or instead; if it's the primary key of the table)