How can I include an aggregated column in a where clause?MySQL - Referencing aggregate column in where clauseHow can I prevent SQL injection in PHP?SQL to find the number of distinct values in a columnSQL join: where clause vs. on clauseHow can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?INNER JOIN ON vs WHERE clauseSQL Server query - Selecting COUNT(*) with DISTINCTSQL Server: How to Join to first rowHow to use count and group by at the same select statementReason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clauseError related to only_full_group_by when executing a query in MySql
Why do Radio Buttons not fill the entire outer circle?
Strong empirical falsification of quantum mechanics based on vacuum energy density?
What to do when eye contact makes your coworker uncomfortable?
How could a planet have erratic days?
Can I cause damage to electrical appliances by unplugging them when they are turned on?
How to make money from a browser who sees 5 seconds into the future of any web page?
What fields between the rationals and the reals allow a good notion of 2D distance?
Can I say "fingers" when referring to toes?
Has any country ever had 2 former presidents in jail simultaneously?
Doesn't the system of the Supreme Court oppose justice?
Pre-mixing cryogenic fuels and using only one fuel tank
What is the highest possible scrabble score for placing a single tile
Microchip documentation does not label CAN buss pins on micro controller pinout diagram
Is there a way to have vectors outlined in a Vector Plot?
Did the UK lift the requirement for registering SIM cards?
The IT department bottlenecks progress, how should I handle this?
What features enable the Su-25 Frogfoot to operate with such a wide variety of fuels?
What does "Scientists rise up against statistical significance" mean? (Comment in Nature)
Short story about a deaf man, who cuts people tongues
Why is the "ls" command showing permissions of files in a FAT32 partition?
Why should universal income be universal?
Why can't the Brexit deadlock in the UK parliament be solved with a plurality vote?
Is this toilet slogan correct usage of the English language?
How many arrows is an archer expected to fire by the end of the Tyranny of Dragons pair of adventures?
How can I include an aggregated column in a where clause?
MySQL - Referencing aggregate column in where clauseHow can I prevent SQL injection in PHP?SQL to find the number of distinct values in a columnSQL join: where clause vs. on clauseHow can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?INNER JOIN ON vs WHERE clauseSQL Server query - Selecting COUNT(*) with DISTINCTSQL Server: How to Join to first rowHow to use count and group by at the same select statementReason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clauseError related to only_full_group_by when executing a query in MySql
I have a 2 column table farmer_crops that stores the relationship between a farmer and the crops they cultivate. A farmer can cultivate either one or multiple crops. The query below gives me the distinct farmers and the total crops they cultivate. How can I get the total number of farmers who cultivate crops above one (my_crops > 1)?
select distinct farmer_id, count(crop_id) as my_crops
from farmer_crops
group by farmer_id
[edit] Using the hint from GMB, the query below gives me a total of 2330 rows which is correct.
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
However, what I really want is the query below, just the total number, but the total_farmers column returns the number 8330 which is wrong.
SELECT count(farmer_id) as total_farmers
FROM farmer_crops
HAVING count(crop_id) > 1
mysql sql
asked Mar 7 at 23:56
mkocanseymkocansey
304
add a comment |
I have a 2 column table farmer_crops that stores the relationship between a farmer and the crops they cultivate. A farmer can cultivate either one or multiple crops. The query below gives me the distinct farmers and the total crops they cultivate. How can I get the total number of farmers who cultivate crops above one (my_crops > 1)?
select distinct farmer_id, count(crop_id) as my_crops
from farmer_crops
group by farmer_id
[edit] Using the hint from GMB, the query below gives me a total of 2330 rows which is correct.
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
However, what I really want is the query below, just the total number, but the total_farmers column returns the number 8330 which is wrong.
SELECT count(farmer_id) as total_farmers
FROM farmer_crops
HAVING count(crop_id) > 1
mysql sql
asked Mar 7 at 23:56
mkocanseymkocansey
304
1
Possible duplicate of MySQL - Referencing aggregate column in where clause
– Nick
Mar 8 at 0:19
add a comment |
I have a 2 column table farmer_crops that stores the relationship between a farmer and the crops they cultivate. A farmer can cultivate either one or multiple crops. The query below gives me the distinct farmers and the total crops they cultivate. How can I get the total number of farmers who cultivate crops above one (my_crops > 1)?
select distinct farmer_id, count(crop_id) as my_crops
from farmer_crops
group by farmer_id
[edit] Using the hint from GMB, the query below gives me a total of 2330 rows which is correct.
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
However, what I really want is the query below, just the total number, but the total_farmers column returns the number 8330 which is wrong.
SELECT count(farmer_id) as total_farmers
FROM farmer_crops
HAVING count(crop_id) > 1
mysql sql
asked Mar 7 at 23:56
mkocanseymkocansey
304
I have a 2 column table farmer_crops that stores the relationship between a farmer and the crops they cultivate. A farmer can cultivate either one or multiple crops. The query below gives me the distinct farmers and the total crops they cultivate. How can I get the total number of farmers who cultivate crops above one (my_crops > 1)?
select distinct farmer_id, count(crop_id) as my_crops
from farmer_crops
group by farmer_id
[edit] Using the hint from GMB, the query below gives me a total of 2330 rows which is correct.
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
However, what I really want is the query below, just the total number, but the total_farmers column returns the number 8330 which is wrong.
SELECT count(farmer_id) as total_farmers
FROM farmer_crops
HAVING count(crop_id) > 1
mysql sql
mysql sql
asked Mar 7 at 23:56
mkocanseymkocansey
304
asked Mar 7 at 23:56
mkocanseymkocansey
304
edited Mar 8 at 16:52
mkocansey
asked Mar 7 at 23:56
mkocanseymkocansey
304
asked Mar 7 at 23:56
mkocanseymkocansey
304
asked Mar 7 at 23:56
mkocanseymkocansey
304
304
1
Possible duplicate of MySQL - Referencing aggregate column in where clause
– Nick
Mar 8 at 0:19
add a comment |
1
Possible duplicate of MySQL - Referencing aggregate column in where clause
– Nick
Mar 8 at 0:19
1
1
Possible duplicate of MySQL - Referencing aggregate column in where clause
– Nick
Mar 8 at 0:19
Possible duplicate of MySQL - Referencing aggregate column in where clause
– Nick
Mar 8 at 0:19
add a comment |
1 Answer
1
active
oldest
votes
Conditions on aggregate expression can not be used in a WHERE clause. You need to use HAVING instead:
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
Unlike the WHERE clause, the HAVING clause comes after the GROUP BY clause.
NB : SELECT DISTINCT usually does not make sense with GROUP BY, I removed it.
answered Mar 8 at 0:01
GMBGMB
18.6k31028
1
Yes theDISTINCTwas pretty much redundant because without or without the query would have given the same results so it can and should be removed for performance.
– Raymond Nijland
Mar 8 at 0:04
Thanks for the hint on using HAVING. This still gives me multiple rows with a farmer and how many crops he cultivates. However, I want to get the total number of farmers doing multiple crops. So this should pretty much return just one row
– mkocansey
Mar 8 at 16:41
@mkocansey: ah ok, I understood a little differently. Well, basically we just need to count the records returned by the other query, so you can simply wrap the query, like : SELECT COUNT(*) FROM ( -- the query -- ) AS x
– GMB
Mar 8 at 17:05
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55054715%2fhow-can-i-include-an-aggregated-column-in-a-where-clause%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Conditions on aggregate expression can not be used in a WHERE clause. You need to use HAVING instead:
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
Unlike the WHERE clause, the HAVING clause comes after the GROUP BY clause.
NB : SELECT DISTINCT usually does not make sense with GROUP BY, I removed it.
answered Mar 8 at 0:01
GMBGMB
18.6k31028
1
Yes theDISTINCTwas pretty much redundant because without or without the query would have given the same results so it can and should be removed for performance.
– Raymond Nijland
Mar 8 at 0:04
Thanks for the hint on using HAVING. This still gives me multiple rows with a farmer and how many crops he cultivates. However, I want to get the total number of farmers doing multiple crops. So this should pretty much return just one row
– mkocansey
Mar 8 at 16:41
@mkocansey: ah ok, I understood a little differently. Well, basically we just need to count the records returned by the other query, so you can simply wrap the query, like : SELECT COUNT(*) FROM ( -- the query -- ) AS x
– GMB
Mar 8 at 17:05
add a comment |
Conditions on aggregate expression can not be used in a WHERE clause. You need to use HAVING instead:
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
Unlike the WHERE clause, the HAVING clause comes after the GROUP BY clause.
NB : SELECT DISTINCT usually does not make sense with GROUP BY, I removed it.
answered Mar 8 at 0:01
GMBGMB
18.6k31028
1
Yes theDISTINCTwas pretty much redundant because without or without the query would have given the same results so it can and should be removed for performance.
– Raymond Nijland
Mar 8 at 0:04
Thanks for the hint on using HAVING. This still gives me multiple rows with a farmer and how many crops he cultivates. However, I want to get the total number of farmers doing multiple crops. So this should pretty much return just one row
– mkocansey
Mar 8 at 16:41
@mkocansey: ah ok, I understood a little differently. Well, basically we just need to count the records returned by the other query, so you can simply wrap the query, like : SELECT COUNT(*) FROM ( -- the query -- ) AS x
– GMB
Mar 8 at 17:05
add a comment |
Conditions on aggregate expression can not be used in a WHERE clause. You need to use HAVING instead:
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
Unlike the WHERE clause, the HAVING clause comes after the GROUP BY clause.
NB : SELECT DISTINCT usually does not make sense with GROUP BY, I removed it.
answered Mar 8 at 0:01
GMBGMB
18.6k31028
Conditions on aggregate expression can not be used in a WHERE clause. You need to use HAVING instead:
SELECT farmer_id, count(crop_id) as my_crops
FROM farmer_crops
GROUP BY farmer_id
HAVING count(crop_id) > 1
Unlike the WHERE clause, the HAVING clause comes after the GROUP BY clause.
NB : SELECT DISTINCT usually does not make sense with GROUP BY, I removed it.
answered Mar 8 at 0:01
GMBGMB
18.6k31028
answered Mar 8 at 0:01
GMBGMB
18.6k31028
answered Mar 8 at 0:01
GMBGMB
18.6k31028
answered Mar 8 at 0:01
GMBGMB
18.6k31028
18.6k31028
1
Yes theDISTINCTwas pretty much redundant because without or without the query would have given the same results so it can and should be removed for performance.
– Raymond Nijland
Mar 8 at 0:04
Thanks for the hint on using HAVING. This still gives me multiple rows with a farmer and how many crops he cultivates. However, I want to get the total number of farmers doing multiple crops. So this should pretty much return just one row
– mkocansey
Mar 8 at 16:41
@mkocansey: ah ok, I understood a little differently. Well, basically we just need to count the records returned by the other query, so you can simply wrap the query, like : SELECT COUNT(*) FROM ( -- the query -- ) AS x
– GMB
Mar 8 at 17:05
add a comment |
1
Yes theDISTINCTwas pretty much redundant because without or without the query would have given the same results so it can and should be removed for performance.
– Raymond Nijland
Mar 8 at 0:04
Thanks for the hint on using HAVING. This still gives me multiple rows with a farmer and how many crops he cultivates. However, I want to get the total number of farmers doing multiple crops. So this should pretty much return just one row
– mkocansey
Mar 8 at 16:41
@mkocansey: ah ok, I understood a little differently. Well, basically we just need to count the records returned by the other query, so you can simply wrap the query, like : SELECT COUNT(*) FROM ( -- the query -- ) AS x
– GMB
Mar 8 at 17:05
1
1
Yes the
DISTINCT was pretty much redundant because without or without the query would have given the same results so it can and should be removed for performance.– Raymond Nijland
Mar 8 at 0:04
Yes the
DISTINCT was pretty much redundant because without or without the query would have given the same results so it can and should be removed for performance.– Raymond Nijland
Mar 8 at 0:04
Thanks for the hint on using HAVING. This still gives me multiple rows with a farmer and how many crops he cultivates. However, I want to get the total number of farmers doing multiple crops. So this should pretty much return just one row
– mkocansey
Mar 8 at 16:41
Thanks for the hint on using HAVING. This still gives me multiple rows with a farmer and how many crops he cultivates. However, I want to get the total number of farmers doing multiple crops. So this should pretty much return just one row
– mkocansey
Mar 8 at 16:41
@mkocansey: ah ok, I understood a little differently. Well, basically we just need to count the records returned by the other query, so you can simply wrap the query, like : SELECT COUNT(*) FROM ( -- the query -- ) AS x
– GMB
Mar 8 at 17:05
@mkocansey: ah ok, I understood a little differently. Well, basically we just need to count the records returned by the other query, so you can simply wrap the query, like : SELECT COUNT(*) FROM ( -- the query -- ) AS x
– GMB
Mar 8 at 17:05
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55054715%2fhow-can-i-include-an-aggregated-column-in-a-where-clause%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Possible duplicate of MySQL - Referencing aggregate column in where clause
– Nick
Mar 8 at 0:19