Styjun

One word for 'the thing that attracts me'?

Set outline first and fill colors later

Is it safe to redirect stdout and stderr to the same file without file descriptor copies?

Why A=2 and B=1 in the call signs for Spirit and Opportunity?

"Official wife" or "Formal wife"?

How to deceive the MC

Are there guidelines for finding good names for LaTeX 2e packages and control sequences defined in these packages?

What happened to the Dothraki in S08E06?

Why do the i8080 I/O instructions take a byte-sized operand to determine the port?

Can a kensei/swashbuckler use an offhand finesse weapon to trigger sneak attack, without using a bonus action?

Can a UK national work as a paid shop assistant in the USA?

Why is 'additive' EQ more difficult to use than 'subtractive'?

How to create a `range`-like iterable object of floats?

Gravitational Force Between Numbers

How to teach an undergraduate course without having taken that course formally before?

Papers on ArXiv as main references

Reduce size of sum sub/superscript?

Split into three!

Is there an idiom that means that you are in a very strong negotiation position in a negotiation?

Writing "hahaha" versus describing the laugh

Did significant numbers of Japanese officers escape prosecution during the Tokyo Trials?

What did the 'turbo' button actually do?

Why isn't Tyrion mentioned in 'A song of Ice and Fire'?

Time complexity of an algorithm: Is it important to state the base of the logarithm?

Why are these two seemingly-similar MySQL queries returning radically different results?

How to output MySQL query results in CSV format?MYSQL: how to return all distinct row in mysql GROUP BY queryMySQL returning different results for DISTINT vs.GROUP BYError in combining two queries in mysqlSelect query returning 1 result instead of 3 because of AVGPHP MySQL query not returning all resultsCombining Selects with two different Group Bys in same queryremoving similar fields from a mysql select query using group byMySQL compatibility or similarity ranking queriesWhy Does a mysql Query Using DAYOFYEAR(DATE) Return Different Results to DAY(DATE)?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I want to pull a list of units that have an overall rating of less than 4. The first query returns 1760 rows with data that seems to check out when cross referenced. The second query returns only 434 rows. Why would this be? I don't fully understand the "Group By" and "Having" clauses, so I'm guessing it has something to do with those.

'''
SELECT u.unitid
,AVG(r.overall) AS AverageRating
, u.ratingcount

FROM reviews r

LEFT JOIN units u
ON u.unitid = r.unitid

-- only retrieve active, non-terminated units set to display
WHERE u.active = 1
AND u.display = 1
AND u.terminated = 0

GROUP BY u.unitid

HAVING AverageRating < 4
;
```
```
SELECT u.UnitID
, u.rating
, u.ratingcount

from units u

WHERE u.rating < 4

-- only retrieve active, non-terminated units set to display
and u.active = 1
and u.display = 1
and u.terminated = 0
;
```

mysql clause

share|improve this question

asked Mar 23 at 21:49

AliasHendricksonAliasHendrickson

41

add a comment | 

0

I want to pull a list of units that have an overall rating of less than 4. The first query returns 1760 rows with data that seems to check out when cross referenced. The second query returns only 434 rows. Why would this be? I don't fully understand the "Group By" and "Having" clauses, so I'm guessing it has something to do with those.

'''
SELECT u.unitid
,AVG(r.overall) AS AverageRating
, u.ratingcount

FROM reviews r

LEFT JOIN units u
ON u.unitid = r.unitid

-- only retrieve active, non-terminated units set to display
WHERE u.active = 1
AND u.display = 1
AND u.terminated = 0

GROUP BY u.unitid

HAVING AverageRating < 4
;
```
```
SELECT u.UnitID
, u.rating
, u.ratingcount

from units u

WHERE u.rating < 4

-- only retrieve active, non-terminated units set to display
and u.active = 1
and u.display = 1
and u.terminated = 0
;
```

mysql clause

share|improve this question

asked Mar 23 at 21:49

AliasHendricksonAliasHendrickson

41

add a comment | 

I want to pull a list of units that have an overall rating of less than 4. The first query returns 1760 rows with data that seems to check out when cross referenced. The second query returns only 434 rows. Why would this be? I don't fully understand the "Group By" and "Having" clauses, so I'm guessing it has something to do with those.

'''
SELECT u.unitid
,AVG(r.overall) AS AverageRating
, u.ratingcount

FROM reviews r

LEFT JOIN units u
ON u.unitid = r.unitid

-- only retrieve active, non-terminated units set to display
WHERE u.active = 1
AND u.display = 1
AND u.terminated = 0

GROUP BY u.unitid

HAVING AverageRating < 4
;
```
```
SELECT u.UnitID
, u.rating
, u.ratingcount

from units u

WHERE u.rating < 4

-- only retrieve active, non-terminated units set to display
and u.active = 1
and u.display = 1
and u.terminated = 0
;
```

mysql clause

share|improve this question

asked Mar 23 at 21:49

AliasHendricksonAliasHendrickson

41

'''
SELECT u.unitid
,AVG(r.overall) AS AverageRating
, u.ratingcount

FROM reviews r

LEFT JOIN units u
ON u.unitid = r.unitid

-- only retrieve active, non-terminated units set to display
WHERE u.active = 1
AND u.display = 1
AND u.terminated = 0

GROUP BY u.unitid

HAVING AverageRating < 4
;
```
```
SELECT u.UnitID
, u.rating
, u.ratingcount

from units u

WHERE u.rating < 4

-- only retrieve active, non-terminated units set to display
and u.active = 1
and u.display = 1
and u.terminated = 0
;
```

share|improve this question

asked Mar 23 at 21:49

AliasHendricksonAliasHendrickson

41

share|improve this question

asked Mar 23 at 21:49

AliasHendricksonAliasHendrickson

41

asked Mar 23 at 21:49

AliasHendricksonAliasHendrickson

41

asked Mar 23 at 21:49

AliasHendricksonAliasHendrickson

41

1 Answer
1

active

oldest

votes

0

Your first query looks at all the records and calculates average rating by Unit ID, and then the HAVING clause limits the final result to records where the average rating by Unit ID is less than 4.

Your second query lists all records where the rating is less than 4, and that's all it does. It doesn't average anything.

Here are a couple passable tutorials I found with a quick Google:

• GROUP BY: https://www.techonthenet.com/sql/group_by.php
  


• GROUP BY and HAVING: https://www.datacamp.com/community/tutorials/group-by-having-clause-sql
  

HAVING filters aggregate values like SUM, AVG, MIN, MAX, etc. It's similar to WHERE, but WHERE only filters non-aggregated values. You can do this:

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
HAVING AVG(Rating) < 4 -- Good: HAVING is for filtering on aggregate values

But you can't do this (only difference from above is WHERE instead of HAVING in the last line):

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
WHERE AVG(Rating) < 4 -- Bad: WHERE is for the raw values, before they're aggregated

Keep at it. You'll get there :)

share|improve this answer

edited Mar 27 at 14:36

answered Mar 23 at 22:36

Ed GibbsEd Gibbs

22k13154

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for your answer Ed!
  
  – AliasHendrickson
  Mar 25 at 18:27
  

  
  
  
  

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55318723%2fwhy-are-these-two-seemingly-similar-mysql-queries-returning-radically-different%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Your first query looks at all the records and calculates average rating by Unit ID, and then the HAVING clause limits the final result to records where the average rating by Unit ID is less than 4.

Your second query lists all records where the rating is less than 4, and that's all it does. It doesn't average anything.

Here are a couple passable tutorials I found with a quick Google:

• GROUP BY: https://www.techonthenet.com/sql/group_by.php
  


• GROUP BY and HAVING: https://www.datacamp.com/community/tutorials/group-by-having-clause-sql
  

HAVING filters aggregate values like SUM, AVG, MIN, MAX, etc. It's similar to WHERE, but WHERE only filters non-aggregated values. You can do this:

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
HAVING AVG(Rating) < 4 -- Good: HAVING is for filtering on aggregate values

But you can't do this (only difference from above is WHERE instead of HAVING in the last line):

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
WHERE AVG(Rating) < 4 -- Bad: WHERE is for the raw values, before they're aggregated

Keep at it. You'll get there :)

share|improve this answer

edited Mar 27 at 14:36

answered Mar 23 at 22:36

Ed GibbsEd Gibbs

22k13154

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for your answer Ed!
  
  – AliasHendrickson
  Mar 25 at 18:27
  

  
  
  
  

add a comment | 

0

Your first query looks at all the records and calculates average rating by Unit ID, and then the HAVING clause limits the final result to records where the average rating by Unit ID is less than 4.

Your second query lists all records where the rating is less than 4, and that's all it does. It doesn't average anything.

Here are a couple passable tutorials I found with a quick Google:

• GROUP BY: https://www.techonthenet.com/sql/group_by.php
  


• GROUP BY and HAVING: https://www.datacamp.com/community/tutorials/group-by-having-clause-sql
  

HAVING filters aggregate values like SUM, AVG, MIN, MAX, etc. It's similar to WHERE, but WHERE only filters non-aggregated values. You can do this:

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
HAVING AVG(Rating) < 4 -- Good: HAVING is for filtering on aggregate values

But you can't do this (only difference from above is WHERE instead of HAVING in the last line):

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
WHERE AVG(Rating) < 4 -- Bad: WHERE is for the raw values, before they're aggregated

Keep at it. You'll get there :)

share|improve this answer

edited Mar 27 at 14:36

answered Mar 23 at 22:36

Ed GibbsEd Gibbs

22k13154

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for your answer Ed!
  
  – AliasHendrickson
  Mar 25 at 18:27
  

  
  
  
  

add a comment | 

Your first query looks at all the records and calculates average rating by Unit ID, and then the HAVING clause limits the final result to records where the average rating by Unit ID is less than 4.

Your second query lists all records where the rating is less than 4, and that's all it does. It doesn't average anything.

Here are a couple passable tutorials I found with a quick Google:

• GROUP BY: https://www.techonthenet.com/sql/group_by.php
  


• GROUP BY and HAVING: https://www.datacamp.com/community/tutorials/group-by-having-clause-sql
  

HAVING filters aggregate values like SUM, AVG, MIN, MAX, etc. It's similar to WHERE, but WHERE only filters non-aggregated values. You can do this:

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
HAVING AVG(Rating) < 4 -- Good: HAVING is for filtering on aggregate values

But you can't do this (only difference from above is WHERE instead of HAVING in the last line):

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
WHERE AVG(Rating) < 4 -- Bad: WHERE is for the raw values, before they're aggregated

Keep at it. You'll get there :)

share|improve this answer

edited Mar 27 at 14:36

answered Mar 23 at 22:36

Ed GibbsEd Gibbs

22k13154

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
HAVING AVG(Rating) < 4 -- Good: HAVING is for filtering on aggregate values

SELECT UnitId, AVG(Rating)
FROM MyTable
GROUP BY UnitId
WHERE AVG(Rating) < 4 -- Bad: WHERE is for the raw values, before they're aggregated

share|improve this answer

edited Mar 27 at 14:36

answered Mar 23 at 22:36

Ed GibbsEd Gibbs

22k13154

answered Mar 23 at 22:36

Ed GibbsEd Gibbs

22k13154

answered Mar 23 at 22:36

Ed GibbsEd Gibbs

22k13154