having

The having clause specifies filtering conditions for groups or group members. It is always used after group by or context by clause.

If having is after group by, it is used with aggregate functions. The result is the rows of groups whose aggregate function values satisfy the specified conditions.

If having is after context by and is used with only aggregate functions, the result is the rows of the members of the groups whose aggregate function values satisfy the specified conditions.

If having is after context by and is used with a function that is not aggregate function, the result is the rows of the records that satisfy the specified conditions.

Examples

sym = `C`MS`MS`MS`IBM`IBM`C`C`C$SYMBOL
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t1 = table(timestamp, sym, qty, price);

t1;

timestamp	sym	qty	price
09:34:07	C	2200	49.6
09:36:42	MS	1900	29.46
09:36:51	MS	2100	29.52
09:36:59	MS	3200	30.02
09:32:47	IBM	6800	174.97
09:35:26	IBM	5400	175.23
09:34:16	C	1300	50.76
09:34:26	C	2500	50.32
09:38:12	C	8800	51.29

select sum(qty) as totalqty from t1 group by sym having sum(qty)>10000;

sym	totalqty
C	14800
IBM	12200

select * from t1 context by sym having count(sym)>2 and sum(qty)>10000;

timestamp	sym	qty	price
09:34:07	C	2200	49.6
09:34:16	C	1300	50.76
09:34:26	C	2500	50.32
09:38:12	C	8800	51.29

select * from t1 context by sym having rank(qty)>1;

timestamp	sym	qty	price
09:34:26	C	2500	50.32
09:38:12	C	8800	51.29
09:36:59	MS	3200	30.02

select * from t1 context by sym having rank(qty)>1 and sum(qty)>10000;

timestamp	sym	qty	price
09:34:26	C	2500	50.32
09:38:12	C	8800	51.29

The row filter in having is performed after calculating functions used in the select statement.

select *, min(qty) from t1 context by sym having rank(qty)>1;

timestamp	sym	qty	price	min_qty
09:34:26	C	2500	50.32	1300
09:38:12	C	8800	51.29	1300
09:36:59	MS	3200	30.02	1900

top clause can be used together with context by clause and having clause.

select top 2 * from t1 context by sym having sum(qty)>8000;

timestamp	sym	qty	price
09:34:07	C	2200	49.6
09:34:16	C	1300	50.76
09:32:47	IBM	6800	174.97
09:35:26	IBM	5400	175.23