Konrad Viltersten Konrad Viltersten - 3 months ago 15
SQL Question

Grouping by a custom column composed of multiple actual columns

I want to display location info constituted by multiple columns in the DB but then I need to group it by the ID. The solution I've got is to list the constituting columns as groupees in the following way.

select
Id,
Name,
Here + ' and ' + There as Location,
count(*) as Count
from KnownStuff
group by Id, Name, Here, There


However, I'd like to know if there a more like-a-bossy way to group by that column, i.d. something along the lines of this.

group by Id, Name, Location


Or, even better (although, based on my googlearching, I'm pretty sure that it's not possible), I'd like to exclude all the other columns except for Id from the grouping constraints. In some cases I'll use sum or some other aggregating function but it'd be nice to just tell the server not to bother And if there are non-identical occurrences, then so be it - let it crash, burn, cry or lie - after all, it's my problem that I wrote a faulty script.

So:


  1. Is there a like-a-bossy approach to grouping a custom column?

  2. Is there a bite-me-in-the-ass-laterish approach to make it easier for now?


Answer

Wrap the query up in a derived table. Do GROUP BY it's result:

select id, name, location, count(*)
from
(
select 
  Id,
  Name,
  Here + ' and ' + There as Location,
from KnownStuff
)
group by Id, Name, location