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
3
votes
4 answers
Trouble with SQLite SQL Query
I'm trying to run the following query in SQLite 3:
SELECT *,
DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
HAVING "distance" <= ?
ORDER BY "distance" ASC;
But I get the following error:
SQLSTATE[HY000]:…
Alix Axel
- 151,645
- 95
- 393
- 500
3
votes
2 answers
SQL HAVING SUM GROUP BY
Using SQL Server 2005. I am building an inventory/purchasing program and I’m at the point where I need the user to “check out” equipment. When he selects a product, I need to query which stock locations have the available Qty, and tell the user…
mhz.NET
- 45
- 1
- 1
- 7
3
votes
3 answers
GROUP BY LAST DATE MYSQL
I have this tables,
user
id
name
visit
id
id_user (fk user.id)
date
comment
If i execute this query,
SELECT u.id, u.name, e.id, e.date, e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id
I get,
1 Jhon 1 2013-12-01 '1st Comment'…
Ricmcm
- 123
- 1
- 1
- 10
3
votes
1 answer
Using the 'case...when...then...else...end' construct in the 'having' clause in JPA criteria query
The following criteria query calculates the average of rating of different groups of products.
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuerycriteriaQuery=criteriaBuilder.createQuery(Tuple.class);
Metamodel…
Tiny
- 27,221
- 105
- 339
- 599
3
votes
3 answers
Oracle ORDER BY with rownum or HAVING >= ALL
My database teacher asked me to write (on Oracle Server) a query: select the groupid with the highest score average for year 2010
I wrote:
SELECT * FROM (
SELECT groupid, AVG(score) average FROM points
WHERE yr = 2010
AND score IS NOT…
Maxux
- 199
- 1
- 1
- 11
3
votes
1 answer
filter SELECTed rows by HAVING by variables value changed in column description
Can anyone explain the differences between following two requests:
SET @foundnow=0;
SELECT id, (@foundnow:=IF(`id`=3,1,0)) as ff
FROM `sometable`
HAVING @foundnow=0
result is
id ff
1 0
2 0
3 1
and
SET…
Ntaxa
- 31
- 2
3
votes
5 answers
Implement SQL function having() in Magento
I need a SQL function "having" in Magento. As far as I know, there is no having function.
So I try to implement it in collection class
public function addAttributeHaving($attribute)
{
$this->getSelect()->having($attribute);
return…
sayasemut
- 51
- 1
- 3
2
votes
1 answer
How to search a value which can returns true in WHERE condition OR in HAVING condition?
I want to search multi vars that could be true in WHERE condition or in HAVING condition in a group_concat table. Something like this:
SELECT
article.id,
article.name,
GROUP_CONCAT(tags.name order by tags.name) AS nameTags,
…
Leo
- 580
- 7
- 22
2
votes
3 answers
SQL Server having criteria for subquery
My query looks like this,
SELECT field1, field2, (SELECT TOP 1 field1 from table1) as field3
FROM table2
HAVING field3 > 0
it throws an error
invalid column name field3
levye
- 235
- 2
- 11
2
votes
2 answers
sql GROUP HAVING selection
I want to project the pid indexes for all products which have the same title, as I'm using the following as a sub query.
Product(pid, title)
SELECT p.title
FROM product p
group by title
HAVING ( COUNT(p.title) > 1 )
this outputs the duplicate…
AlasdairC
- 190
- 1
- 1
- 14
2
votes
2 answers
Count email type per customer id
I have a form that people can use to send emails to our clients. The user has an option to select between two canned messages (Message "1" or Message "2"). Behind the scenes, every time they hit the "SEND" button it logs into a "RECORDS" table (so…
RS3
- 194
- 1
- 3
- 15
2
votes
1 answer
rollup ignores group by having constraint
+----+-------+-------+
| id | style | color |
+----+-------+-------+
| 1 | 1 | red |
| 2 | 1 | blue |
| 3 | 2 | red |
| 4 | 2 | blue |
| 5 | 2 | green |
| 6 | 3 | blue |
+----+-------+-------+
The query:
SELECT…
osoviejo
- 481
- 6
- 17
2
votes
1 answer
Converting complex mysql SELECT into UPDATE
I have been trying to convert a complex SELECT query to an UPDATE query, but I keep getting the 1064 syntax error.
The goal for the query is to update certain rows which meet particular conditions, however the joins and the GROUP BY and HAVING…
Ruben
- 89
- 7
2
votes
1 answer
Using AVG with HAVING
I looked at a similar question and read the documentation on the average function, but when I tried:
CREATE TABLE tab(
id INT,
score INT
);
INSERT INTO tab VALUES
(1, 22),
(2, 45),
(3, 82),
(4, 87);
SELECT score,AVG(score)
FROM tab
GROUP…
JMP
- 4,417
- 17
- 30
- 41
2
votes
2 answers
Find keys in a sqlite database that meet intersection of conditions
It's hard for me to summarize the query I want to make, so maybe an example will make it clearer.
Let's say I have two primary tables:
employees:
| employee_id | employee_name |
| ----------- | ------------- |
| 1 | Alice |
| 2 …
Nathan Pierson
- 5,461
- 1
- 12
- 30