More GROUP BY

Question

Post reply

More GROUP BY

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
March 23, 2016 at 11:26 pm

#74447

Comments posted to this topic are about the item More GROUP BY
March 23, 2016 at 11:27 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jamsheer Ten Centuries Points: 1136 More actions · Answer 1

Jamsheer

Ten Centuries

Points: 1136

March 24, 2016 at 1:22 am

#1867327

Easy.. Thanks

l.vignando Old Hand Points: 313 More actions · Answer 2

Nice question, but awarding points for and titling "Group by" is a big hint, so alternatives without group should be automatically excluded.

Anyway nice question and nice count distinct trick.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

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.
Anyway nice question and nice count distinct trick.

Nice question. However, I've seen using the title as a hint produce the wrong result before. Yes, it was a hint here, but not always. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

dlaughlin 24148 Old Hand Points: 355 More actions · Answer 4

The explanation excludes the DISTINCT keyword, which might be confusing if you didn't already know the correct answer.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

Interesting question. The wrong answers were obvious. But the right answer I had to think about. I've never constructed a query like that before, using distinct in the HAVING clause's COUNT. I've used it in a simple select COUNT(distinct..., just not with the HAVING part.

Revenant SSC-Forever Points: 42467 More actions · Answer 6

Revenant

SSC-Forever

Points: 42467

March 24, 2016 at 8:12 am

#1867437

Really, really easy. Thanks, Steve!

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 7

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.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 8

Shouldn't it be COUNT(DISTINCT Played) >= 3, in case some decides to play 'switch kick to the nuts'? 😛

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 9

Nice question, Steve. However, it won't work if the players are playing Rock, Paper, Scissors, Lizard, Spock. 🙂

stephanie.butler Grasshopper Points: 15 More actions · Answer 10

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.

Good catch!

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 11

stephen.long.1 (3/24/2016)
Nice question, Steve. However, it won't work if the players are playing Rock, Paper, Scissors, Lizard, Spock. 🙂

I deliberately left that out for another day.

starunit SSCommitted Points: 1817 More actions · Answer 12

Good easy way to end the week (wishing it was Friday), but the query would error in all cases:

the table is created as [RPSGame], but the FROM clause references [RPSGames]. Too bad/so sad.

So ha! I caught the trick!

Mark
Just a cog in the wheel.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 13

starunit (3/24/2016)
Good easy way to end the week (wishing it was Friday), but the query would error in all cases:
the table is created as [RPSGame], but the FROM clause references [RPSGames]. Too bad/so sad.
So ha! I caught the trick!

Thanks. Corrected.