Why does Location=emptystring occupy two rows on "GROUP BY Location" on a data.stackexchan-CodePudding

Why does Location=emptystring occupy two rows on this data.stackexchange query?

CodePudding user response：

If you try this verison you'll see Location has both blank string and null values

SELECT SUM(Reputation) AS rep, 
  Location, 
  IIF(location is null, 1, 0) NullLocation
FROM Users
GROUP BY Location, iif(location is null, 1, 0)
ORDER BY rep DESC;

You can IsNull / Coalesce to sum both together:

SELECT SUM(Reputation) AS rep, 
  ISNULL(Location, '') Location
FROM Users
GROUP BY isnull(Location, '')
ORDER BY rep DESC;