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]
956 questions
271
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,570
- 5
- 28
- 33
236
votes
8 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
- 7,159
- 20
- 54
- 73
198
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
- 29,316
- 9
- 57
- 81
170
votes
9 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
150
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
- 1,903
- 4
- 16
- 20
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
53
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
- 10,920
- 8
- 45
- 83
41
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
- 473
- 1
- 5
- 7
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
22
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,577
- 4
- 31
- 45
18
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,435
- 13
- 35
- 45
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
- 5,905
- 7
- 29
- 34
16
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
- 4,292
- 5
- 28
- 43
14
votes
5 answers
Using a HAVING clause in an UPDATE statement
This query
SELECT
FirstName, LastName, NCAAStats.AccountId, College_Translator.school_name, StatTypeId, COUNT(*) AS 'Count'
FROM NCAAstats
INNER JOIN College_Translator
ON College_Translator.AccountID = NCAAstats.AccountId
GROUP BY FirstName,…
Tyler DeWitt
- 22,191
- 36
- 108
- 189
12
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
- 181
- 2
- 12