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;