mysql查询构建从单列获取2个不同的数据

Guys i m having a table in which i have gender and date columns as described. now i wan to count both male n females registered in particular month,so how can i make my query for that.

伙计们我有一张桌子,其中我有所描述的性别和日期列。现在我要计算特定月份注册的男性和女性,所以我怎样才能对此进行查询。

mysql> select id,gender,entrydate from patient_master;

+----+--------+------------+
| id | gender | entrydate  |
+----+--------+------------+
|  1 | Male   | 2012-07-02 |
|  2 | Female | 2012-05-10 |
|  3 | Male   | 2012-05-25 |
|  4 | Female | 2012-07-09 |
|  5 | Male   | 2012-07-10 |
|  6 | Female | 2012-07-10 |
|  7 | Male   | 2012-07-10 |
+----+--------+------------+

4 个解决方案

#1

To get the results for all months:

要获得所有月份的结果:

SELECT YEAR(entrydate) AS yr, MONTH(entrydate) AS mnth, gender, COUNT(*) AS cnt
FROM patient_master
GROUP BY YEAR(entrydate), MONTH(entrydate), gender

If you want it for all months of a single year:

如果你想要一年的所有月份:

SELECT MONTH(entrydate) AS mnth, gender, COUNT(*) AS cnt
FROM patient_master
WHERE YEAR(entrydate) = 2012
GROUP BY MONTH(entrydate), gender

#2

select gender, count(id) as`count` 
from patient_master
where month(entrydate) = 1 and year(entrydate) = 2012
group by gender

Month 1 = January, 2 = February...

第1个月= 1月,2 = 2月......

#3

@juergen_d is right.

@juergen_d是对的。

His query would return this:

他的查询会返回:

+--------+--------+
| gender | count  |
+--------+--------+
| Male   |  1     |
| Female |  1     |
+--------+--------+

#4

SELECT
    date_format(entrydate,'%Y-%m-%b') YearMonth,
    gender,COUNT(1) GenderCount
FROM
    patient_master
GROUP BY
    date_format(entrydate,'%Y-%m-%b'),gender
;

Here is your sample data

这是您的示例数据

mysql> CREATE TABLE patient_master
    -> (
    ->     id int not null auto_increment,
    ->     gender varchar(10),
    ->     entrydate date,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO patient_master (gender,entrydate) VALUES
    -> ('Male'  ,'2012-07-02'),
    -> ('Female','2012-05-10'),
    -> ('Male'  ,'2012-05-25'),
    -> ('Female','2012-07-09'),
    -> ('Male'  ,'2012-07-10'),
    -> ('Female','2012-07-10'),
    -> ('Male'  ,'2012-07-10');
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

Here is the output

这是输出

mysql>     SELECT
    ->         date_format(entrydate,'%Y-%m-%b') YearMonth,
    ->         gender,COUNT(1) GenderCount
    ->     FROM
    ->         patient_master
    ->     GROUP BY
    ->         date_format(entrydate,'%Y-%m-%b'),gender
    ->     ;
+-------------+--------+-------------+
| YearMonth   | gender | GenderCount |
+-------------+--------+-------------+
| 2012-05-May | Female |           1 |
| 2012-05-May | Male   |           1 |
| 2012-07-Jul | Female |           2 |
| 2012-07-Jul | Male   |           3 |
+-------------+--------+-------------+
4 rows in set (0.02 sec)

mysql>

#1