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]
489 questions
254
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,181
- 4
- 24
- 35
121
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
51
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
- 12,874
- 29
- 103
- 169
32
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
- 492
- 1
- 4
- 11
28
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
- 607
- 2
- 10
- 17
14
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
- 6,796
- 19
- 77
- 121
12
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
- 237,063
- 85
- 359
- 638
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
- 451
- 1
- 6
- 21
10
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
- 942
- 4
- 12
- 20
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
- 690
- 4
- 21
- 50
7
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
- 75
- 1
- 2
- 6
7
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
- 602
- 1
- 11
- 28
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,355
- 1
- 25
- 39
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
- 219
- 1
- 4
- 15
6
votes
4 answers
Difference between WHERE and HAVING in SQL
Possible Duplicate:
SQL: What's the difference between HAVING and WHERE?
I have seen various discussions on WHERE and HAVING. I still have a question: is HAVING used only when considering aggregates, or can it be used in more general terms:…
CuriousKen
- 69
- 1
- 1
- 2