/    Sign up×
Community /Pin to ProfileBookmark

Two counts one column one query

Copy linkTweet thisAlerts:
Dec 06.2020

I have a bunch or rows. Let’s say they have an indicator in there. Like male and female.

I want to do a query based on another index ID in the where clause.

COUNT(males) and COUNT(females)

The column is gender 1=male, 2=female.

How can I do this in a single query.

to post a comment
PHP

7 Comments(s) ↴

Copy linkTweet thisAlerts:
@SempervivumDec 06.2020 — I'm not a PHP nor SQL expert and I don't know about a pure-SQL solution, however when using PDO you can group the result as described here:

https://stackoverflow.com/questions/5361716/is-there-a-way-to-fetch-associative-array-grouped-by-the-values-of-a-specified-c

and then count the results like this:
$res = $pdo->query('SELECT status FROM attendancetracking');
$result = $res->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
var_dump($result);
$count1 = count($result[1]);
$count2 = count($result[2]);
var_dump($count1, $count2);
(I used some table I already had in my database, you need to replace the column name by "gender" and the table name).
Copy linkTweet thisAlerts:
@SempervivumDec 06.2020 — PS: In the meantime I remembered that this can be done more easily by use of a group clause:
$res = $pdo->query('SELECT status, COUNT(status) FROM attendancetracking GROUP BY status');
$result = $res->fetchAll();
var_dump($result);
$count1 = $result[0][1];
$count2 = $result[1][1];
var_dump($count1, $count2);
Copy linkTweet thisAlerts:
@sibertDec 06.2020 — > @kiwis80#1625647 COUNT(males) and COUNT(females)

> The column is gender 1=male, 2=female.

> How can I do this in a single query.

Try:

``<i>
</i>SELECT
(SELECT count(1) FROM table WHERE gender=1) as MALE,
(SELECT count(1) FROM table WHERE gender=2) as FEMALE<i>
</i>``
Copy linkTweet thisAlerts:
@NogDogDec 06.2020 — While I can imagine some interesting(?) ways to do it with multiple joins on the same table, I think @sibert's approach is probably the way to go. Fleshing it out a bit for PDO, maybe:
<i>
</i>$sql = "
SELECT
(SELECT COUNT(id) FROM the_table WHERE some_id = :some_id AND gender = 1) AS male,
(SELECT COUNT(id) FROM the_table WHERE some_id = :some_id AND gender = 2) AS female
";
$stmt = $pdo-&gt;prepare($sql);
$stmt-&gt;execute([':some_id' =&gt; $some_id]);
Copy linkTweet thisAlerts:
@SempervivumDec 06.2020 — @NogDog#1625658 Why do you favor sibert's solution over my second one? It needs **two** subqueries why mine does the job by one.
Copy linkTweet thisAlerts:
@sibertDec 06.2020 — > @Sempervivum#1625661 It needs two subqueries why mine does the job by one.

Your query is smart, but it depends on whether you want 2 rows or 2 columns...
Copy linkTweet thisAlerts:
@NogDogDec 06.2020 — @Sempervivum#1625661 I think the only reason is that it gets both values in one result row. It's a trivial preference, and I wouldn't lose any sleep if the OP went with your solution. :) I just kind of like letting the DB do the work when it can (and it's not too complex/weird/inefficient).
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.16,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...