Seley: Salle, Damn I don’t get it

Correl: No_gravity: Don’t you know you can sort by expression?

Barringer: Anything to do with null values ?

Correl: No_gravity: s/sort/ORDER BY

Sreaves: F1 + NULL is null right ?

Correl: Wrksx: All aggregate functions ignore NULLs. Yes that’s it

Dossey: Salle, got it = but 1 + null = null is kinda weird, never got used to it

Correl: Wrksx: Here it is again with the data: http://pastie.org/10428539

Britschgi: No_gravity: http://sqlfiddle.com/#!9/7207a/8

Elwood: Cool, i didn’t know you could do maths in the select statement. thanks !

Correl: Wrksx: And to make it even more clear: http://pastie.org/10428540

Homsey: Danblack: that sorts water in the wrong position null here

Correl: Aias: You can do lot more in SELECT part

Correl: Aias: In fact that is one of the main reasons for relational databases to exist: You don’t select what is stored there, but rather generate different data using what is stored

Leafe: Salle: not sure if i made the example clear enough. here i added some infos: http://sqlfiddle.com/#!9/7207a/9

Correl: Aias: Same with SUMx by the way. SUM is not stored in the database

Correl: No_gravity: You can ORDER BY colum = ‘foo, column; for example and thus control the order of appearance of certain values

Correl: No_gravity: FIELD function can help with more complicated cases

Correl: No_gravity: MySQL sorts NULLs as lower than values so it comes first with ASC and last with DESC

Correl: Wrksx: If both columns are defined as NOT NULL then indeed SUMc1 + SUMc2 and SUMc1+c2 are guaranteed to be equal

Bissada: Somebody came up with this monster: http://sqlfiddle.com/#!9/7207a/14

Candanoza: Might do the right thing, but its pretty heavy.

Wiederholt: Salle, sure, I had this exact issue some month ago. My total price was nulled by one of it’s component tax beeing null sometimes

Figurski: Now I know = but it bugged me quite a few hours

Sica: No_gravity: http://sqlfiddle.com/#!9/7207a/16 – except without duplicating?

Sumners: Danblack: well, thats waaayy different from the desired output

Izzi: I think its better to do it application side then

Correl: Wrksx: NULLs can drive you crazy if you forget them :

Paone: No_gravity, if you got time to do so, consider enhancing you DB schema

Qadeer: Wrksx: how? i have the feeling the schema is good

Lavigne: No_gravity, having categoy for both drinks and info seems weird to me

Porteus: And cetegories should go in another ‘categories’ table

Montore: So you reference their id

Arkenberg: Which makes thing easier on the long term

Schwall: Wrksx: hmm. no, for this application its better to have one table for all products.

Leaks: No_gravity, you don’t get it

Correl: Wrksx: My all time favorite: http://pastie.org/10428587

Swed: No_gravity, having “soda” in two tables once with a NULL category and second time with th “drinks” category is exactly the “update anomaly” which database normalization is supposed to help you remove

Porten: Salle, aaaaaah horrible

Gotto: Salle, and what about countf1 ?

Wellborn: Null would still count as 1 ?

Bossert: I thought they would be totally ignored

Correl: Swed: Can you explain it?

Swed: Salle, I expect the variable which controls how nulls are compared was changed?

Correl: I included variant of this question in first MySQL certification exam when we introduced it back in MySQL 4.0 times

Correl: Swed: Nope no such variable

Swed: Salle, yep, looking now, mixed it with “myisam_stats_method”