noname005 noname005 - 7 months ago 8
SQL Question

MYSQL Multiple COUNT() With Single Query

I want to get total ROW types with single query. Let me explain it.

Normally I use this query to get one of them:

$database->query("SELECT COUNT(*) as c FROM workers WHERE job = 1");
$totalWorkers = $database->fetchObject()->c;


So, it works flawless but there 7 more job types (0-8). This is just (1).

I can do like that: [TOO MUCH QUERY TO EXECUTE, NOT GOOD]

$database->query("SELECT COUNT(*) as c FROM workers WHERE job = 1");
$totalWorkers1 = $database->fetchObject()->c;

$database->query("SELECT COUNT(*) as c FROM workers WHERE job = 2");
$totalWorkers2 = $database->fetchObject()->c;


But I don't want to execute these multiple queries, is there any easy way to do that? I want to get results of count like that:

$totalWorkers2 = $database->fetchObject()->type_1;
$totalWorkers2 = $database->fetchObject()->type_2;


I've searched it everywhere but I couldn't find & think any logic for that ...

Answer
SELECT sum(IF(job=1,1,0)) as job1count, sum(IF(job=2,1,0)) as job2count FROM workers;

Or

SELECT job, count(*) as c FROM workers where job in (1,2) GROUP BY job;

Depending if you want them in the same record or not

Comments