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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer