统计分组中多列数据

一, 问题

按tid(身份编号)分组, 统计每组中新用户数和老用户数    

二, 建表, 初始化数据

-- drop table test1;
CREATE TABLE `test1` (
	`id` INT (11),
	`name` VARCHAR (60),
	`birth` DATETIME ,
	`tid` VARCHAR (60)
); 
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('1','a','2013-07-01 00:00:00','11');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('2','b','2013-06-01 00:00:00','22');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('3','c','2013-08-01 00:00:00','11');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('4','d','2013-07-01 00:00:00','22');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('5','e','2013-08-01 00:00:00','11');

SELECT * FROM test1;

三, 分别求新老用户

-- 老用户: ~ 2013-07-01
SELECT tid, birth
FROM test1
WHERE birth < '2013-07-01'

-- 新用户: 2013-07-01 ~ 
SELECT tid, birth
FROM test1
WHERE birth >= '2013-07-01'

四, 一次求出个分组中新老用户数

-- 求新老用户
SELECT 	tid AS '省份', 
	COUNT(CASE WHEN birth < '2013-07-01' THEN 1 ELSE NULL END) AS '老用户',
	COUNT(CASE WHEN birth >= '2013-07-01' THEN 1 ELSE NULL END) AS '新用户'	
FROM test1
GROUP BY tid

五, 全部 sql

-- drop table test1;
CREATE TABLE `test1` (
	`id` INT (11),
	`name` VARCHAR (60),
	`birth` DATETIME ,
	`tid` VARCHAR (60)
); 
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('1','a','2013-07-01 00:00:00','11');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('2','b','2013-06-01 00:00:00','22');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('3','c','2013-08-01 00:00:00','11');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('4','d','2013-07-01 00:00:00','22');
INSERT INTO `test1` (`id`, `name`, `birth`, `tid`) VALUES('5','e','2013-08-01 00:00:00','11');

SELECT * FROM test1;


-- 老用户: ~ 2013-07-01
SELECT tid, birth
FROM test1
WHERE birth < '2013-07-01'

-- 新用户: 2013-07-01 ~ 
SELECT tid, birth
FROM test1
WHERE birth >= '2013-07-01'


-- 求新老用户
SELECT 	tid AS '省份', 
	COUNT(CASE WHEN birth < '2013-07-01' THEN 1 ELSE NULL END) AS '老用户',
	COUNT(CASE WHEN birth >= '2013-07-01' THEN 1 ELSE NULL END) AS '新用户'	
FROM test1
GROUP BY tid