asked on

sql mysql count with condition

SELECT COUNT( * ) AS `Row` , `Hno`
FROM `member1`
GROUP BY

How can I count the number of person in each house

Hno is the (house number)
Unit is the group of house
village

CREATE TABLE `member1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Hno` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  `Sname` varchar(80) COLLATE utf8_bin DEFAULT NULL,
  `Ssurname` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `Unit` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `village` ENUM('Angtong','Namoung','Lipanoi','Talingam','Maenam','Maret','Bophut') COLLATE utf8_bin DEFAULT NULL,
  `Lastrow` ENUM('0','1') COLLATE utf8_bin DEFAULT '0',
  `DateUp` datetime DEFAULT NULL,
  `TimeAdd` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `remark1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `datenote` date DEFAULT NULL,
  `remark2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `useradd` varchar(80) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY  (`id`),
  KEY `Sname` (`Sname`),
  KEY `Ssurname` (`Ssurname`)
) ENGINE=InnoDB AUTO_INCREMENT=36904 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Open in new window

Terry Woods

Is this what you want?

Select Hno, count(*)
from member1
group by Hno

Open in new window

MrTV

ASKER

Select Hno, count(*)
from member1
group by Hno

when i do like this the house that stay in different village will count together

ASKER CERTIFIED SOLUTION

Terry Woods

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial