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
15
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
- 23,366
- 38
- 119
- 196
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
12
votes
1 answer
MySQL Update Subset Having
I have three tables: contacts, domains, and contacts_domains, which form a many-to-many relationship.
I would like to run a query that updates the contacts_domains table, but only for domains that have exactly one contact.
I know how to SELECT the…
Charles
- 853
- 3
- 8
- 21
11
votes
1 answer
SQL Having on columns not in SELECT
I have a table with 3 columns:
userid mac_address count
The entries for one user could look like this:
57193 001122334455 42
57193 000C6ED211E6 15
57193 FFFFFFFFFFFF 2
I want to create a view that displays only those MAC's that are…
Rapsey
- 599
- 2
- 7
- 17
10
votes
3 answers
Hibernate Criteria API - HAVING clause work arounds
I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum is greater or equal to a particular value.
Normally I would use a HAVING clause in my…
user57701
- 213
- 2
- 4
- 7
10
votes
6 answers
Mysql: how to select groups having certain values?
Say there is such table:
mysql> SELECT * FROM tags;
+---------+--------+
| post_id | tag_id |
+---------+--------+
| 1 | 2 |
| 1 | 3 |
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
+---------+--------+
5 rows…
htf
- 1,503
- 4
- 15
- 21
10
votes
2 answers
Does MySQL eliminate common subexpressions between SELECT and HAVING/GROUP BY clause
I often see people answer MySQL questions with queries like this:
SELECT DAY(date), other columns
FROM table
GROUP BY DAY(date);
SELECT somecolumn, COUNT(*)
FROM table
HAVING COUNT(*) > 1;
I always like to give the column an alias and refer to…
Barmar
- 741,623
- 53
- 500
- 612
9
votes
2 answers
Retrieving records fulfilling a condition using GROUP BY
I'd like to only select the rows where the count is greater than 1 (in other words the duplicates) right now from a few thousand records I am mostly seeing 1s with a few 2s and 3s here and there
SELECT count( * ) AS `Number` , GI . *
FROM…
Moak
- 12,596
- 27
- 111
- 166
8
votes
3 answers
Java Streams GroupingBy and filtering by count (similar to SQL's HAVING)
Do Java (9+) streams support a HAVING clause similar to SQL? Use case: grouping and then dropping all groups with certain count. Is it possible to write the following SQL clause as Java stream?
GROUP BY id
HAVING COUNT(*) > 5
The closest I could…
knittl
- 246,190
- 53
- 318
- 364
8
votes
4 answers
Is it possible to combine Group by, Having and Sum?
I have a table:
------------------------
|id|p_id|desired|earned|
------------------------
|1 | 1 | 5 | 7 |
|2 | 1 | 15 | 0 |
|3 | 1 | 10 | 0 |
|4 | 2 | 2 | 3 |
|5 | 2 | 2 | 3 |
|6 | 2 | 2 | 3 …
S.ork
- 229
- 2
- 13
8
votes
3 answers
Performance implications of allowing alias to be used in HAVING clause
I made a bit of a fool out of myself earlier today on this question. The question was using SQL Server, and the correct answer involved adding a HAVING clause. The initial mistake I made was to think that an alias in the SELECT statement could be…
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
8
votes
3 answers
Mysql - Conditional Group By
I am trying to use CASE and GROUP BY together to conditionally filter results only if they match the CASE criteria, and if they don't, return results as if there were no GROUP BY criteria specified.
Here's a simple model of what I have:
es.id |…
Hamking
- 155
- 1
- 2
- 8
7
votes
2 answers
SELECT with GROUP_CONCAT, GROUP BY, HAVING
I Have table with odd_id and i want to select combinations for different ticket_id's. Here's my query:
SELECT
ticket_id,
GROUP_CONCAT(odd_id) as oddsconcat
FROM ticket_odds
GROUP BY ticket_id
And it gives me Following:
'28',…
user474470
7
votes
0 answers
How do I write a GROUP BY or HAVING clause in Diesel?
I'm trying to convert the following SQL query into corresponding Rust Diesel code:
SELECT COUNT(*)
FROM BookStore
WHERE BookName IN ('Lord of the Rings', 'Hobbit')
GROUP BY StoreId
HAVING COUNT(DISTINCT BookName) = 2
I was able to translate it thus…
user1842633
- 305
- 1
- 4
- 15
7
votes
2 answers
MySQL GROUP BY...HAVING different values same field
I want to use a query similar to the following to retrieve all rows in events that have at least one corresponding event_attendances row for 'male' and 'female'. The below query returns no rows (where there certainly are some events that have…
Will
- 1,893
- 4
- 29
- 42