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
4
votes
1 answer
Oracle - Why HAVING clause can be before GROUP BY
Why/How HAVING clause can be before GROUP BY
select count(1) from tableA
having id >1
group by id
order by count(1)
I know HAVING clause can be without GROUP BY clause,
But when defining GROUP BY why HAVING isn't forced so to be after, as ORDER BY…
Ori Marko
- 56,308
- 23
- 131
- 233
4
votes
0 answers
Postgresql: HAVING statement that skips rows which are null or empty?
Is there any way to skip rows that are null or empty? I could use some help with sorting the output of a subtable. My having statement is returning an error I can’t fix.
It returns the error: ERROR: invalid input syntax for integer: " "
This…
M. Albasi
- 41
- 1
4
votes
1 answer
SQL Server how can I use COUNT DISTINCT(*) in HAVING clause?
I have a procedure that counts all the unique [customerid] values and displays them in a SELECT list. I'm trying to sort the [customerid] where it is only "> 1" by using a HAVING clause, but SQL won't let me use the DISTINCT COUNT inside the HAVING.…
Gerrit Botes
- 57
- 1
- 2
- 6
4
votes
4 answers
MySQL error: Non-grouping field is used in HAVING clause
Is there a way to correct this query so that it works with ONLY_FULL_GROUP_BY enabled?
SELECT LOWER(s) AS lower_s, SUM(i) AS sum_i
FROM t
GROUP BY 1
HAVING LENGTH(lower_s) < 5
It gives the error message
Non-grouping field 'lower_s' is used in…
AndreKR
- 32,613
- 18
- 106
- 168
4
votes
7 answers
SQL Server - Top Saleperson Per Region
SELECT region, person, sum(dollars) as thousands
FROM sales
GROUP BY region, person
ORDER BY region, sum(dollars) desc
The SQL above produces a complete list of sales people per region like this
region person thousands
…
user246211
- 1,137
- 1
- 13
- 23
4
votes
4 answers
Django queryset - Adding HAVING constraint
I have been using Django for a couple of years now but I am struggling today with adding a HAVING constraint to a GROUP BY.
My queryset is the following:
crm_models.Contact.objects\
.filter(dealercontact__dealer__pk__in=(265,),
…
Q Caron
- 952
- 13
- 26
4
votes
0 answers
Select specific columns from subquery in Nhibernate Criteria
How Can I translate the following SQL query to Criteria query:
Select A.TextProperty, B.TextProperty, Count(C.Id)
From A, B, C
Where A.Id In
(
Select A.Id, C.Id, Count(C.Id)
From A, C
Having Count(C.Id) > 1
)
I know the Where part is…
Ruba
- 867
- 3
- 11
- 19
4
votes
2 answers
Doctrine - COUNT rows when a HAVING clause is used
How could I count rows in a query with a HAVING clause?
I have a query to fetch paginated data like below (but little more complicated):
$qb = $this->em->createQueryBuilder()
->select('p')
->from('Application\Entity\Modules_ProductVersions',…
Jacek Kaniuk
- 5,229
- 26
- 28
4
votes
3 answers
MySQL Searching in Many-to-Many
I've begun working on my first MySQL database, and I've run into a simple problem.
I have my movies categorized by genre in a 'many-to-many' relationship:
'movies' Table
+---------+-------------------+
|movie_id |movie_title …
JProffitt
- 157
- 9
4
votes
1 answer
getting the count of distinct duplicate ids in mysql
this is the query
select count(*),
ss.pname,
ttu.user_id,
ttl.location_name ,
group_concat(em.customer_id),
count(em.customer_id)
from seseal as ss,
track_and_trace_user as ttu,
…
ashish
- 99
- 1
- 7
3
votes
2 answers
Restricting results to only rows where one value appears only once
I have a query that is more complex than the example here, but which needs to only return the rows where a certain field doesn't appear more than once in the data set.
ACTIVITY_SK STUDY_ACTIVITY_SK
100 200
101 201
102 …
user1183688
- 43
- 1
- 3
3
votes
5 answers
is there something faster than "having count" for large tables?
Here is my query:
select word_id, count(sentence_id)
from sentence_word
group by word_id
having count(sentence_id) > 100;
The table sentenceword contains 3 fields, wordid, sentenceid and a primary key id.
It has 350k+ rows.
This query takes a…
Jeff
- 717
- 2
- 8
- 19
3
votes
1 answer
How do I use a "HAVING" clause in a Zend_Db_Select statement without parenthesis?
I know Zend provides a having() method, but what I want is a query like:
SELECT a.*, `as`.* FROM `fruit_db`.`apples` AS `a`
INNER JOIN `fruit_db`.`apple_seeds` AS `as` ON a.id = as.apple_id
WHERE (a.id = 1) AND as.seed_name HAVING 'johnny'
not…
thecheese
- 31
- 1
- 3
3
votes
2 answers
tsql distinct having count
I am using SSMS 2008 and am trying to select count of consumers who are part of two different events. Probably this is a simple query, but it is currently not returning expected count. Here is my T-SQL code which better explains what I…
salvationishere
- 3,461
- 29
- 104
- 143
3
votes
1 answer
Why do the rows with 0 disappear when I use having?
I have this tables:
create table series(
serie varchar(10),
season varchar(10),
chapter varchar(10),
primary key ( serie, season, chapter)
);
insert into series values ('serie_1', 'season_1', 'Chap_1'),
…
cnd
- 33
- 4