Manross: Culbreath: think of GROUP BY, HAVING and LIMIT as filters on the resultset
Neslusan: Iese: Most implementations of sql don’t allow the mixing of grouped and ungrouped columns in the SELECT clause. mysql does, but the row selected for the ungrouped column is an unpredictable value. The groupwise max solution is a way to specify the value selected for the ungrouped column.
Culbreath: Manross: I’m comfortable with that much. just trying to speak more formally about it
Culbreath: Let’s say I use GROUP BY color
Culbreath: Then these records: ‘jack’, ‘red’ ‘jane’, ‘red’ ‘joe, ‘red’ should be grouped into a single. ?
Manross: Culbreath: if you’re trying to use HAVING to repair the indeterminate result of an abused GROUP BY, may Xenu have mercy on your soul
Culbreath: What do I call the single *** ? An aggregate? Another row?
Manross: Culbreath: any of those human names are indeterminate. they may show up on this query, and maybe a different one on another query
Culbreath: Will this return 3, ***uming the only red users in the DB are the ones I wrote above: SELECT Count* From People GROUP BY color HAVING name=’jane’
Manross: Culbreath: if you’re trying to use HAVING to repair the indeterminate result of an abused GROUP BY, may Xenu have mercy on your soul
Culbreath: Manross: It’s just a thought experiment to verify to myself my understanding sof ar
Ulloa: Culbreath: The where filter the result first, then ‘group by’ put these results into groups and ‘having’ filter these groups. why are you goruping by color and having anme?
Culbreath: Xor1: I’m just trying to write queries and discuss their outcomes.
Culbreath: Xor1: I get that it’s a terrible way to structure such a thing
Varieur: Culbreath: don’t waste time with invalid sql.
Culbreath: Varieur: I’m in a course, not sure I have that luxury =
Varieur: Culbreath: SELECT name, COUNT* AS cnt FROM tbl GROUP BY name HAVING cnt 1
Varieur: Culbreath: use WHERE to filter on existing columns. HAVING for the result of aggregation functions.
Manross: Culbreath: remind your instructor that all RDBMSs strive to match the SQL standard, all fail, and all add their own extensions.
Varieur: Culbreath: your instructor should attempt to teach you proper sql and theory.
Culbreath: To his credit, it’s only been a single lecture so far
Varieur: Culbreath: showing incorrect sql will actually give out a bad example and inject incorrect habits for new sql users.
Varieur: Culbreath: SELECT name, COUNT* AS cnt FROM tbl WHERE name IN ‘foo’, ‘bar’ GROUP BY name HAVING cnt 1
Varieur: Culbreath: that’s a far more complete example to show to new users.
Culbreath: Varieur: lol I really appreciate all the help you’re giving me. What I’m focused on right now is understanding a few specific queries before correcting them though.
Varieur: Culbreath: do you understand the query I just gave you?
Manross: Culbreath: this url on mysql’s acknowledged sins against the canon deserves reading before your next cl***. https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
Culbreath: Varieur: let me digest it, 1 sec
Culbreath: Varieur: I believe so, yes
Culbreath: Manross: It’s counting the number of foos, and the number of bars, and returning two rows
Varieur: Culbreath: so now you know what COUNT, GROUP BY, WHERE and HAVING does.
Culbreath: Is HAVING cnt 1 necessary?
Varieur: Culbreath: so you should be able to trivially digest the other incorrect sql given to you by your misguided teacher, and point out the mistakes.
Varieur: Culbreath: it’s just one application of the HAVING clause.
Shido: Culbreath: find them with SELECT COUNT* as qty,dupefield,otherfields FROM table GROUP BY dupefield,otherfields HAVING qty 1
Yakulis: Select * from chat where nickname like ‘%Varieur%’;
Manross: Culbreath: select sumcol_name = ‘foo’ as my_foo_count, sumcol_name = ‘bar’ as my_bar_count from t1; — grok that.