drop table if exists t1,t2; create table t1 (a int); select count(a) as b from t1 where a=0 having b > 0; b insert into t1 values (null); select count(a) as b from t1 where a=0 having b > 0; b select count(a) as b from t1 where a=0 having b >=0; b 0 explain extended select count(a) as b from t1 where a=0 having b >=0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select count(`test`.`t1`.`a`) AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 0) having (count(`test`.`t1`.`a`) >= 0) drop table t1; CREATE TABLE t1 ( raw_id int(10) NOT NULL default '0', chr_start int(10) NOT NULL default '0', chr_end int(10) NOT NULL default '0', raw_start int(10) NOT NULL default '0', raw_end int(10) NOT NULL default '0', raw_ori int(2) NOT NULL default '0' ); INSERT INTO t1 VALUES (469713,1,164123,1,164123,1),(317330,164124,317193,101,153170,1),(469434,317194,375620,101,58527,1),(591816,375621,484273,1,108653,1),(591807,484274,534671,91,50488,1),(318885,534672,649362,101,114791,1),(318728,649363,775520,102,126259,1),(336829,775521,813997,101,38577,1),(317740,813998,953227,101,139330,1),(1,813998,953227,101,139330,1); CREATE TABLE t2 ( id int(10) unsigned NOT NULL default '0', contig_id int(10) unsigned NOT NULL default '0', seq_start int(10) NOT NULL default '0', seq_end int(10) NOT NULL default '0', strand tinyint(2) NOT NULL default '0', KEY id (id) ); INSERT INTO t2 VALUES (133195,469713,61327,61384,1),(133196,469713,64113,64387,1),(133197,1,1,1,0),(133197,1,1,1,-2); SELECT e.id, MIN( IF(sgp.raw_ori=1, (e.seq_start+sgp.chr_start-sgp.raw_start), (sgp.chr_start+sgp.raw_end-e.seq_end))) as start, MAX( IF(sgp.raw_ori=1, (e.seq_end+sgp.chr_start-sgp.raw_start), (sgp.chr_start+sgp.raw_end-e.seq_start))) as end, AVG(IF (sgp.raw_ori=1,e.strand,(-e.strand))) as chr_strand FROM t1 sgp, t2 e WHERE sgp.raw_id=e.contig_id GROUP BY e.id HAVING chr_strand= -1 and end >= 0 AND start <= 999660; id start end chr_strand 133197 813898 813898 -1.0000 drop table t1,t2; CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); select Fld1, max(Fld2) as q from t1 group by Fld1 having q is not null; Fld1 q 1 20 3 50 select Fld1, max(Fld2) from t1 group by Fld1 having max(Fld2) is not null; Fld1 max(Fld2) 1 20 3 50 select Fld1, max(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; Fld1 max(Fld2) 1 20 3 50 select Fld1, max(Fld2) from t1 group by Fld1 having std(Fld2) is not null; Fld1 max(Fld2) 1 20 3 50 select Fld1, max(Fld2) from t1 group by Fld1 having variance(Fld2) is not null; Fld1 max(Fld2) 1 20 3 50 drop table t1; create table t1 (id int not null, qty int not null); insert into t1 values (1,2),(1,3),(2,4),(2,5); select id, sum(qty) as sqty from t1 group by id having sqty>2; id sqty 1 5 2 9 select sum(qty) as sqty from t1 group by id having count(id) > 0; sqty 5 9 select sum(qty) as sqty from t1 group by id having count(distinct id) > 0; sqty 5 9 drop table t1; CREATE TABLE t1 ( `id` bigint(20) NOT NULL default '0', `description` text ) ENGINE=MyISAM; CREATE TABLE t2 ( `id` bigint(20) NOT NULL default '0', `description` varchar(20) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (1, 'test'); INSERT INTO t2 VALUES (1, 'test'); CREATE TABLE t3 ( `id` bigint(20) NOT NULL default '0', `order_id` bigint(20) NOT NULL default '0' ) ENGINE=MyISAM; select a.id, a.description, count(b.id) as c from t1 a left join t3 b on a.id=b.order_id group by a.id, a.description having (a.description is not null) and (c=0); id description c 1 test 0 select a.*, count(b.id) as c from t2 a left join t3 b on a.id=b.order_id group by a.id, a.description having (a.description is not null) and (c=0); id description c 1 test 0 INSERT INTO t1 VALUES (2, 'test2'); select a.id, a.description, count(b.id) as c from t1 a left join t3 b on a.id=b.order_id group by a.id, a.description having (a.description is not null) and (c=0); id description c 1 test 0 2 test2 0 drop table t1,t2,t3; drop table if exists wl1972; create table wl1972 ( col1 int, col2 varchar(5) character set latin1 collate latin1_bin) engine=innodb; insert into wl1972 values(10,'hello'); insert into wl1972 values(20,'hello'); insert into wl1972 values(30,'hello'); insert into wl1972 values(10,'bye'); insert into wl1972 values(10,'sam'); insert into wl1972 values(10,'bob'); select count(*) from wl1972 group by col1 having col1 = 10; count(*) 4 select count(*) as count_col1 from wl1972 group by col1 having col1 = 10; count_col1 4 select count(*) as count_col1 from wl1972 as t1 group by col1 having col1 = 10; count_col1 4 select count(*) from wl1972 group by col2 having col2 = 'hello'; count(*) 3 select count(*) from wl1972 group by col2 having col1 = 10; ERROR 42S22: Unknown column 'col1' in 'having clause' select col1 as count_col1 from wl1972 as t1 group by col1 having col1 = 10; count_col1 10 select col1 as count_col1 from wl1972 as t1 group by col1 having count_col1 = 10; count_col1 10 select col1 as count_col1 from wl1972 as t1 group by count_col1 having col1 = 10; count_col1 10 select col1 as count_col1 from wl1972 as t1 group by count_col1 having count_col1 = 10; count_col1 10 select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col1 = 10; count_col1 col2 10 bob 10 bye 10 hello 10 sam select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having count_col1 = 10; count_col1 col2 10 bob 10 bye 10 hello 10 sam select col1 as count_col1,col2 from wl1972 as t1 group by col1,col2 having col2 = 'hello'; count_col1 col2 10 hello 20 hello 30 hello select col1 as count_col1,col2 as group_col2 from wl1972 as t1 group by col1,col2 having group_col2 = 'hello'; count_col1 group_col2 10 hello 20 hello 30 hello select sum(col1) as co12 from wl1972 group by col2 having col2 10; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10' at line 1 select sum(col1) as co2, count(col2) as cc from wl1972 group by col1 having col1 =10; co2 cc 40 4 select t1.col1 from wl1972 as t1 where t1.col2 in (select t2.col2 from wl1972 as t2 group by t2.col1,t2.col2 having t2.col1<=10); col1 10 20 30 10 10 10 select t1.col1 from wl1972 as t1 where t1.col2 in (select t2.col2 from wl1972 as t2 group by t2.col1,t2.col2 having t2.col1<= (select min(t3.col1) from wl1972 as t3)); col1 10 20 30 10 10 10 drop table wl1972; create table wl1972 (s1 int); insert into wl1972 values (1),(2),(3); select count(*) from wl1972 group by s1 having s1 is null; count(*) select s1*0 as s1 from wl1972 group by s1 having s1 <> 0; s1 select s1*0 from wl1972 group by s1 having s1 = 0; s1*0 select s1 from wl1972 group by 1 having 1 = 0; s1 select count(s1) from wl1972 group by s1 having count(1+1)=2; count(s1) select count(s1) from wl1972 group by s1 having s1*0=0; count(s1) 1 1 1 select * from wl1972 a, wl1972 b group by a.s1 having s1 is null; ERROR 23000: Column 's1' in having clause is ambiguous drop table wl1972; create table wl1972 (s1 char character set latin1 collate latin1_german1_ci); insert into wl1972 values ('ΓΌ'),('y'); Warnings: Warning 1265 Data truncated for column 's1' at row 1 select s1,count(s1) from wl1972 group by s1 collate latin1_swedish_ci having s1 = 'y'; s1 count(s1) y 1 drop table wl1972;