Solved

sql mysql count with condition

Posted on 2008-06-10
3
608 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:teera
  • 2
3 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 21757190
Is this what you want?
Select Hno, count(*)
from member1
group by Hno

Open in new window

0
 

Author Comment

by:teera
ID: 21757271
Select Hno, count(*)
from member1
group by Hno

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

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 21757292
Perhaps this:
Select Hno, Unit, village, count(*)
from member1
group by Hno, Unit, village

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question