MySQL Forums
Forum List  »  General

Re: Count two different values from the same column
Posted by: laptop alias
Date: December 02, 2010 06:47AM

CREATE TABLE members
(member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) NOT NULL
,gender CHAR(1) NOT NULL
);

INSERT INTO members VALUES 
(1,'John','m'),
(2,'Yoko','f'),
(3,'Paul','m'),
(4,'Linda','f'),
(5,'George','m'),
(6,'Ringo','m');

 SELECT * FROM members;
+-----------+--------+--------+
| member_id | name   | gender |
+-----------+--------+--------+
|         1 | John   | m      |
|         2 | Yoko   | f      |
|         3 | Paul   | m      |
|         4 | Linda  | f      |
|         5 | George | m      |
|         6 | Ringo  | m      |
+-----------+--------+--------+

SELECT gender
     , COUNT(*) total 
  FROM members 
 GROUP  
    BY gender;
+--------+-------+
| gender | total |
+--------+-------+
| f      |     2 |
| m      |     4 |
+--------+-------+

SELECT SUM(CASE WHEN gender = 'm' THEN 1 ELSE 0 END) m
     , SUM(CASE WHEN gender = 'f' THEN 1 ELSE 0 END) f
  FROM members;
+------+------+
| m    | f    |
+------+------+
|    4 |    2 |
+------+------+

Options: ReplyQuote


Subject
Written By
Posted
Re: Count two different values from the same column
December 02, 2010 06:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.