Group by on a concatenate field

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by ?

How can I group by the field that I selected as CODE?

you have to repeat the expression:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

|||Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

But... why do you add an empty string in between? And do you mean LIKE 'F%', rather than LIKE 'F' ?

Don't underestimate the power of table expressions. The optimiser knows what you mean (so there's very little difference to performance), and you can easily make something far more readable that way. You could also have used a CTE, like this:

with c as (select CONVERT(VarChar(10),Service_Prefix)+ '' +

CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount,

Service_Prefix from

dbo.Enterprise_Credits_Import_90_days)
select top 100 code, date_issued, sum(amount)

from c
where code <> '0'

and Service_Prefix like 'F'

group by code, date_issued

Rob|||

Rob Farley wrote:

Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

SQL Server does not allow grouping by column alias names - does it? Other RDB engines allow this, and even ordinal grouping (group by 1,2, ...) notations but I thought SQL Server required you to repeat the expressions.

|||But this isn't actually grouping by a column alias. It's grouping by a field in a table expression. It would be no different if you created a view which had those fields in it - then you wouldn't see a problem using the view's fields to group by...

By wrapping the fields up in a table expression, you can easily circumnavigate the restrictions on SQL Server to repeat those expressions.

Rob|||right - I should have looked more closely at your original query - I didn't see the inline view before...|||:) So has it helped?|||

Im going to test it today. Ill let you know if I get it to work.

Thanks for alll the help !

|||

I tried the first statement and was able to make it work. Thanks for the info.

The second query gave me this error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.

Also due to the fact that the database has around 6 million records the group by's seem to make the query take a while to run. Is there any way to speed up the query?

Thanks again for your help

|||The second one requires SQL2005 - you'll get an error in SQL2000.

Could you get away with grouping by service_prefix and service_code separately, and then only concatenating them in the final select? That way, you could put a useful index on those two fields (plus date_issued, and amount), and it should run much more nicely. A covering index which includes all the fields you're interested in will mean that it doesn't even need to look at the table, because all the info will exist in the index.

Like this:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code <> ' 0' and Service_Prefix like 'F%'
group by service_code, service_prefix, date_issued

And you have an index on service_code, service_prefix, date_issued, amount

By changing the where clause to filter on the service_code rather than the concatenated field, that will help performance too, because it can then use the index more effectively. If you need to cater for where the 0 can be either in the prefix or in the code, you could always do an extra check... but try to avoid grouping or filtering on calculated fields, which makes it harder for the system to use indexes.

Rob|||

I think I see what you are saying here. The group by on both non concatenated fields should produce the same results as one group by by using the indexes more effectively. Ill try this and let you know. Prob be tommorow. Although in order to do a group by I thought you had to have the the group by fields in the select statement?

Thanks for your help.

|||You don't need to have the group by fields in the select statement, you just can't use fields that aren't either aggregated or one of the grouped fields.

But you can certainly select a concatenation of two of the grouped fields - definitely no problem there.

Rob|||

I would be careful about grouping on such a value as this:

Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

Instead of doing all of this conversion stuff in the bowels of the query do it either:

1. In the user interface and write the query as:

Select TOP 100 Service_Prefix, Service_Code, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

2. If you cannot use the UI, then do the conversion in an aggregate. There will be little performance hit because there will only be a single row in every case:

Select TOP 100 Max(CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code)) as CODE,
Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

ANY code appearing in an expression in a where or group by clause (and having, join-on criteria, etc) can cause performance issues that cannot be solved with indexes, like the criteria:

CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0'

Will not perform well because you don't have an index on this expression, and the optimizer would have to figure it out over and over instead of a simple probe into an index.

|||Yes... this is the point I was trying to make.

One thing to do it as an intellectual exercise about what you can do with T-SQL, but as soon as you're talking about performance, then you need to consider the fact that you really don't want to use the results of functions for filters/groups/sorts.

Rob|||Not disagreeing with you...Just adding my 2 cents worth to clarify/back you up :)