MYSQL Multiple COUNT() With Single Query

Question

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

I know, there is very easy way to do that, but I can't think about it. — Liveth, Apr 22 '16 at 20:42
Why do it with a single query? Get it working first, optimize the critical sections later. — BoltBait, Apr 22 '16 at 20:45
I always optimize my queries because there are like 1,5 million records...... — Liveth, Apr 22 '16 at 20:46
Only 1.5M records? Sounds like a small database. I doubt you'd see much time taken with the second run as the database would be cached at that point. — BoltBait, Apr 22 '16 at 20:48

score 1 · Accepted Answer · answered Apr 22 '16 at 20:45

1

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

answered Apr 22 '16 at 20:45

JNevill

46,980
4
38
63

Good idea on the first example to align with the requested results from the OP – bitfiddler Apr 22 '16 at 20:51
WOW! Thank you so much sir. That was really really good answer! And quick! – Liveth Apr 22 '16 at 20:57
The first query could be simplified: `SELECT SUM(job = 1), SUM(job = 2) ...` – shmosel Apr 22 '16 at 21:07
@shmosel, `SUM(job = 1)` can return `NULL` instead of `0` if no row with `job=1` exists. – Paul Spiegel Apr 22 '16 at 21:37
@PaulSpiegel, `job = 1` evaluates to 0 (aka `FALSE`) if `job` is any value other than 1 or `NULL`. So your statement is only correct if all values are `NULL`, or if there are no results at all (in which case any `SUM()` variation will return `NULL`). Of course if that's an issue, just do `IFNULL(SUM(...), 0)`. – shmosel Apr 22 '16 at 22:28
@shmosel, you're right - my mistake. Don't know what i thought - I'm using that shortcut too. – Paul Spiegel Apr 22 '16 at 22:36

score 0 · Answer 2 · answered Apr 22 '16 at 20:48

You can group results by job

$database->query("SELECT job, COUNT(*) as c FROM workers WHERE job in (1,2) group by job");

$tmp = $database->fetchAll();

$result = array();

foreach ($tmp as $o) $result[$o->job] = $o->c;

And then use array $result as $result[1] for job=1, $result[2] for job=2

You can remove filter by job WHERE job in (1,2) if you need counts for all jobs

MYSQL Multiple COUNT() With Single Query

2 Answers2