COUNT with a condition for a GROUP SELECT

[Joseph Hesse]

I want to do a "COUNT" with a condition for a GROUP SELECT. Here is my
example.

--------------
DESCRIBE Persons
--------------

+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| LName | varchar(45) | NO | | NULL | |
| Age | int(11) | NO | | NULL | |
| Gender | enum('Male','Female') | NO | | NULL | |
+--------+-----------------------+------+-----+---------+-------+

--------------
SELECT * FROM Persons
--------------

+--------+-----+--------+
| LName | Age | Gender |
+--------+-----+--------+
| Smith | 18 | Male |
| Jones | 18 | Female |
| Martin | 18 | Male |
| Burns | 19 | Male |
+--------+-----+--------+

--------------
SELECT Age, COUNT(*) AS Total, COUNT(Gender='Male') AS Men,
COUNT(Gender='Female') AS Women
FROM Persons
GROUP BY Age
--------------

+-----+-------+-----+-------+
| Age | Total | Men | Women |
+-----+-------+-----+-------+
| 18 | 3 | 3 | 3 |
| 19 | 1 | 1 | 1 |
+-----+-------+-----+-------+

The above output should show that there are 2 Males and 1 Female who are
age 18. This is not the case.

Thank you,
Joe

[Thomas 'PointedEars' Lahn]

Joseph Hesse wrote:

> --------------
> SELECT * FROM Persons
> --------------
>
> +--------+-----+--------+
> | LName | Age | Gender |
> +--------+-----+--------+
> | Smith | 18 | Male |
> | Jones | 18 | Female |
> | Martin | 18 | Male |
> | Burns | 19 | Male |
> +--------+-----+--------+
>
> --------------
> SELECT Age, COUNT(*) AS Total, COUNT(Gender='Male') AS Men,

^^^^^^^^^^^^^

> COUNT(Gender='Female') AS Women

^^^^^^^^^^^^^^^

> FROM Persons
> GROUP BY Age
> --------------
>
> +-----+-------+-----+-------+
> | Age | Total | Men | Women |
> +-----+-------+-----+-------+
> | 18 | 3 | 3 | 3 |
> | 19 | 1 | 1 | 1 |
> +-----+-------+-----+-------+
>
> The above output should show that there are 2 Males and 1 Female who are
> age 18. This is not the case.

So, how did you get the idea that was how COUNT(…) worked (in MySQL)?

<http://dev.mysql.com/doc/refman/5.6/en/group-by-
functions.html#function_count>

> Thank you,

You are welcome.

--
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[Salvatore]

You'd be better off GROUPing by age for the totals, then by LEFT JOINing
the two other columns. Unfortunately, the only way I am able to write a
query that gives the correct data is to use two subqueries:

SELECT pp.Age AS Age, Total, pp.Men AS Men, COUNT(cw.Age) AS Women
FROM (
SELECT p.Age AS Age, p.Total AS Total, COUNT(cm.Age) AS Men
FROM (
SELECT Age, COUNT(Age) AS Total FROM Persons GROUP BY Age
) AS p
LEFT JOIN Persons cm
ON cm.Age = p.Age
AND cm.Gender = 'Male'
GROUP BY cm.Age)
AS pp
LEFT JOIN Persons cw
ON cw.Age = pp.Age
AND cw.Gender = 'Female'
GROUP BY cw.Age
ORDER BY pp.Age ASC;

I'm sure there's a much simpler way to write this.

--
Blah blah bleh...
GCS/CM d(-)@>-- s+:- !a C++$ UBL++++$ L+$ W+++$ w M++ Y++ b++

[Erick T. Barkhuis]

Joseph Hesse:

>--------------
>SELECT Age, COUNT(*) AS Total, COUNT(Gender='Male') AS Men,
>COUNT(Gender='Female') AS Women FROM Persons
>GROUP BY Age
>--------------

As Pointed Ears kindly and politely replied, this is not the proper
way. But I assume that you wrote this mainly as semi-code in order to
make your thoughts clear to c.d.m.

>
>+-----+-------+-----+-------+
>> Age | Total | Men | Women |
>+-----+-------+-----+-------+
>> 18 | 3 | 3 | 3 |
>> 19 | 1 | 1 | 1 |
>+-----+-------+-----+-------+
>
>The above output should show that there are 2 Males and 1 Female who
>are age 18. This is not the case.

Here's my attempt (untested on your data):

SELECT Age,
COUNT(*) AS Total,

SUM( IF(Gender="Male", 1, 0) ) AS Men,
SUM( IF(Gender="Female", 1, 0) ) AS Women

FROM Persons
GROUP BY Age


--

Erick

[Erick T. Barkhuis]

Erick T. Barkhuis:

>Joseph Hesse:

>>+-----+-------+-----+-------+
>>> Age | Total | Men | Women |
>>+-----+-------+-----+-------+
>>> 18 | 3 | 3 | 3 |
>>> 19 | 1 | 1 | 1 |
>>+-----+-------+-----+-------+

>Here's my attempt (untested on your data):
>
>SELECT Age,
> COUNT(*) AS Total,
> SUM( IF(Gender="Male", 1, 0) ) AS Men,
> SUM( IF(Gender="Female", 1, 0) ) AS Women
>FROM Persons
>GROUP BY Age

....and you may want to add
ORDER BY Age
to get the result set in the desired order.

--
Erick

[Lennart Jonsson]

On 04/25/2013 10:12 PM, Joseph Hesse wrote:
[...]

> SELECT Age, COUNT(*) AS Total, COUNT(Gender='Male') AS Men,
> COUNT(Gender='Female') AS Women
> FROM Persons
> GROUP BY Age
>

, count(case when gender = 'Male' then 1 end) as #male


/Lennart

[...]

[Thomas 'PointedEars' Lahn]

----------------------------------------------------^

Assuming syntactically correct code before the comma, there is a syntax
error in MySQL 5.5.30-1.1 (Debian) because single-line comments start with
“#” in MySQL, so that the effective statement is

… , count(case when gender = 'Male' then 1 end) as

(The rest of the code is correct and working.)

MySQL also has an IF function (similar to Microsoft Jet SQL's “IIf(…)”):

… , COUNT(IF(`gender` = 'Male', 1, 0)) AS `male`

<http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html>