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]
1156 questions
285
votes
18 answers
What is the difference between HAVING and WHERE in SQL?
What is the difference between HAVING and WHERE in an SQL SELECT statement?
EDIT: I have marked Steven's answer as the correct one as it contained the key bit of information on the link:
When GROUP BY is not used, HAVING behaves like a WHERE…
ColinYounger
- 6,735
- 5
- 31
- 33
281
votes
9 answers
SQL - HAVING vs. WHERE
I have the following two tables:
1. Lecturers (LectID, Fname, Lname, degree).
2. Lecturers_Specialization (LectID, Expertise).
I want to find the lecturer with the most Specialization.
When I try this, it is not working:
SELECT
L.LectID,
…
Adam Sh
- 8,137
- 22
- 60
- 75
247
votes
4 answers
SQL query for finding records where count > 1
I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per day with the same account number.
UPDATE: Additionally,…
Benjamin Muschko
- 32,442
- 9
- 61
- 82
202
votes
10 answers
MySQL - Using COUNT(*) in the WHERE clause
I am trying to accomplish the following in MySQL (see pseudo code)
SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) > 10
ORDER BY lastupdated DESC
Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a…
Ice
171
votes
3 answers
Linq with group by having count
how do I write this query in linq (vb.net)?
select B.Name
from Company B
group by B.Name
having COUNT(1) > 1
Fernando
- 2,123
- 4
- 17
- 21
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
64
votes
4 answers
MySQL joins and COUNT(*) from another table
I have two tables: groups and group_members.
The groups table contains all the information for each group, such as its ID, title, description, etc.
In the group_members table, it lists all the members who are apart of each group like…
hohner
- 11,498
- 8
- 49
- 84
55
votes
3 answers
PostgreSQL WHERE count condition
I have following query in PostgreSQL:
SELECT
COUNT(a.log_id) AS overall_count
FROM
"Log" as a,
"License" as b
WHERE
a.license_id=7
AND
a.license_id=b.license_id
AND
b.limit_call > overall_count
GROUP BY
…
Hadi
- 613
- 1
- 6
- 7
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
23
votes
1 answer
... where count(col) > 1
I have a table like this:
+-----+-----+-------+
| id | fk | value |
+-----+-----+-------+
| 0 | 1 | peter |
| 1 | 1 | josh |
| 3 | 2 | marc |
| ... | ... | ... |
I'd like now to get all entries which have more than one value.
The…
cimnine
- 3,987
- 4
- 33
- 49
21
votes
3 answers
PostgreSQL - Aliases column and HAVING
SELECT
CASE WHEN SUM(X.Count)*3600 is null THEN '0'
ELSE
SUM(X.Count)*3600
END AS PJZ,
X.Mass
FROM X
WHERE X.Mass > 2000
HAVING ((X.Mass / PJZ * 100) - 100) >= 10;
Getting: ERROR: Column »pjz«…
6EQUJ5HD209458b
- 271
- 1
- 3
- 12
20
votes
5 answers
It's possible to have a WHERE clause after a HAVING clause?
Is it possible to use a WHERE clause after a HAVING clause?
The first thing that comes to my mind is sub queries, but I'm not sure.
P.S. If the answer is affirmative, could you give some examples?
cc.
- 5,533
- 14
- 37
- 46
19
votes
1 answer
Having clause vs subquery
I could write a query using an aggregate function in two ways:
select team, count(min) as min_count
from table
group by team
having count(min) > 500
or
select *
from (
select team, count(min) as min_count
from table
group by team
) as…
ferics2
- 5,241
- 7
- 30
- 46
16
votes
2 answers
MySQL GROUP BY and HAVING
I'm grouping my results based on a column X and I want to return the rows that has highest Column Y's value in the group.
SELECT *
FROM mytable
GROUP BY col1
HAVING col2 >= (SELECT MAX(col2)
FROM mytable AS mytable2
…
Elie
- 6,915
- 7
- 31
- 35
16
votes
2 answers
DELETE FROM HAVING COUNT(*) in MySQL
Ok so there are couple posts here already on this and fewer still out on the web. I've literally tried every one of them and can not get anything to work. Hopefully someone here can take pity on me :)
Here is the data I'm working with. I want to…
RFQ Master
- 205
- 1
- 3
- 10