About the HAVING keyword in SQL.
(Should really be: sql-having)
The HAVING clause is the equivalent of WHERE after applying aggregate functions.
Reference
Related tags
Questions tagged [having-clause]
494 questions
275
votes
7 answers
WHERE vs HAVING
Why do you need to place columns you create yourself (for example select 1 as "number") after HAVING and not WHERE in MySQL?
And are there any downsides instead of doing WHERE 1 (writing the whole definition instead of a column name)?
baloo
- 7,635
- 4
- 27
- 35
126
votes
5 answers
Can you use an alias in the WHERE clause in mysql?
I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following…
user15063
60
votes
4 answers
use mysql SUM() in a WHERE clause
suppose I have this table
id | cash
1 200
2 301
3 101
4 700
and I want to return the first row in which the sum of all the previous cash is greater than a certain value:
So for instance, if I want to return the first row in which the…
kamikaze_pilot
- 14,304
- 35
- 111
- 171
41
votes
1 answer
Unknown column in 'having clause'
I need to find in sakila database the longest rental period of a movie.
I have tried this:
SELECT DISTINCT
customer.first_name
FROM
rental,
customer
WHERE
rental.customer_id = customer.customer_id
GROUP BY
…
mike
- 767
- 2
- 10
- 18
40
votes
2 answers
What is the correct way to do a HAVING in a MongoDB GROUP BY?
For what would be this query in SQL (to find duplicates):
SELECT userId, name FROM col GROUP BY userId, name HAVING COUNT(*)>1
I performed this simple query in MongoDB:
res = db.col.group({key:{userId:true,name:true},
reduce:…
shlomoid
- 573
- 1
- 4
- 11
15
votes
4 answers
SELECT id HAVING maximum count of id
Have a products table with item_id and color_id. I'm trying to get the color_id with the most non-null instances.
This fails:
SELECT color_id
FROM products
WHERE item_id=1234
GROUP BY item_id
HAVING MAX(COUNT(color_id))
with
Invalid…
a coder
- 7,530
- 20
- 84
- 131
14
votes
4 answers
how to use SQL group to filter rows with maximum date value
I have the following table
CREATE TABLE Test
(`Id` int, `value` varchar(20), `adate` varchar(20))
;
INSERT INTO Test
(`Id`, `value`, `adate`)
VALUES
(1, 100, '2014-01-01'),
(1, 200, '2014-01-02'),
(1, 300, '2014-01-03'),
(2,…
NewtonCode
- 1,322
- 5
- 14
- 25
14
votes
6 answers
Is the HAVING clause redundant?
The following two queries yield the exact same result:
select country, count(organization) as N
from ismember
group by country
having N > 50;
select * from (
select country, count(organization) as N
from ismember
group by country) x
where N >…
fredoverflow
- 256,549
- 94
- 388
- 662
11
votes
4 answers
HOW to use HAVING COUNT(*) with hibernate
I need to create a query and I need COUNT(*) and HAVING COUNT(*) = x.
I'm using a work around that uses the CustomProjection class, that I downloaded somewhere.
This is the SQL that I try to achieve:
select count(*) as y0_, this_.ensayo_id as y1_…
Nicolas400
- 563
- 1
- 7
- 29
9
votes
3 answers
Find not unique rows in Oracle SQL
I have a question which looks easy but I can't figure it out.
I have the following:
Name Zipcode
ER 5354
OL 1234
AS 1234
BH 3453
BH 3453
HZ 1234
I want to find those rows where the ID does not define clearly one row.
So…
SüniÚr
- 826
- 1
- 16
- 33
8
votes
4 answers
Is there a "NOT HAVING" syntax like "WHERE XXX NOT IN"?
I have a few queries get the ID numbers of rows that will be deleted in the future.
The row numbers are put into a string and placed in the query below (where you see "2").
I want the results to ignore the rows (as though they have already been…
Drewneedshelp
- 85
- 1
- 2
- 6
7
votes
12 answers
GROUP BY another table that have been grouped with two sub query
I have table like this
Table1
ID | Val | Val2 |
606541 |3175031503131004|3175032612900004|
606542 |3175031503131004|3175032612900004|
677315 |3175031503131004|3175032612980004|
222222 …
Gagantous
- 432
- 6
- 29
- 69
7
votes
4 answers
Why do you have 'where' when there is 'having'
I know this is much discussed, but none of my research could convince me the difference between 'where' and 'having' clauses in MySQL. From what I understand we can achieve everything that can be done with 'where' clause using 'having' . For eg.…
Sethunath K M
- 4,702
- 3
- 28
- 42
7
votes
3 answers
Understanding the GROUP BY statement's behaviour
The question is this..
Table is this..
+--------------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra …
Jonathan Tizard
- 229
- 1
- 4
- 15
6
votes
1 answer
HAVING clause without GROUP BY in Oracle database using developer desktop and developer web
My understanding as per standard practice is that HAVING is to be used along with GROUP BY for filtering conditions, while WHERE is supposed to be used for general row-wise filtering conditions.
However, there are online discussions with mixed…
KarthikS
- 883
- 1
- 11
- 17