More GROUP BY
-
Thanks for the question.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Hi Steve, am I misreading this?
The correct answer is recognised as:-
GROUP BY Person HAVING COUNT(DISTINCT Played) = 3
I agree with that.
But then in the subsequent explanation, one of the other (wrong answers) is quoted:-
Thus, we use this query:
SELECT
rg.Person
FROM
dbo.RPSGame AS rg
GROUP BY rg.Person
HAVING COUNT(rg.Played) = 3
This will return a user that has at least 3 rows with different values for the "played" category.
That won't return a user that has at least three rows with different values for "played".
It will return a user that has played at least three games regardless of what they play.
-
Andy Hogg (3/25/2016)
Hi Steve, am I misreading this?The correct answer is recognised as:-
GROUP BY Person HAVING COUNT(DISTINCT Played) = 3
I agree with that.
But then in the subsequent explanation, one of the other (wrong answers) is quoted:-
Thus, we use this query:
SELECT
rg.Person
FROM
dbo.RPSGame AS rg
GROUP BY rg.Person
HAVING COUNT(rg.Played) = 3
This will return a user that has at least 3 rows with different values for the "played" category.
That won't return a user that has at least three rows with different values for "played".
It will return a user that has played at least three games regardless of what they play.
Correct. Explanation was wrong. It's corrected.
-
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
ZZartin (3/24/2016)
Shouldn't it be COUNT(DISTINCT Played) >= 3, in case some decides to play 'switch kick to the nuts'? 😛Good point.
-
l.vignando (3/24/2016)
Nice question, but awarding points for and titling "Group by" is a big hint, so alternatives without group should be automatically excluded.By the same token, a whole lot of people have such a misunderstanding of "Group By" and resort to other methods to try to accomplish the task. It's a good question that covers several real life attempts by those that don't know.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Joel Ewald (3/24/2016)
I think with the current data model and lack of constraints you would actually want both.WHERE Played = 'Rock' OR Played = 'Paper' OR Played = 'Scissors'
GROUP BY Person HAVING COUNT(DISTINCT Played) = 3
but the group by is key to the solution.
Heh... my next point was going to be that if "Played" were not constrained in some fashion, there would be no guarantee that only Rock, Paper, or Scissors would be present and the GROUP BY could return an incorrect condition. I think that's even more important than how to possibly solve the stated problem and, in real life, would consider it to be a missing requirement.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply