SQLTeam.com | Weblogs | Forums

How to create a new column and group by that column in a selection?

Hello!

Can you tell me please why this does not work:

select
CASE WHEN c.[1] is null then c.[2] else c.[1] end as a, count(distinct c.[3])
from [t] c
WHERE c.[5]=''
group by a

It cannot recognize a, but I want to group by a!

Thanks!

Column aliases are not defined references, they're just a nickname for an expression. You can do this:

SELECT
CASE WHEN c.[1] IS NULL THEN c.[2] ELSE c.[1] END as a, COUNT(DISTINCT c.[3])
FROM [t] c
WHERE c.[5]=''
GROUP BY CASE WHEN c.[1] IS NULL THEN c.[2] ELSE c.[1] END

Or:

;WITH CTE AS (SELECT CASE WHEN c.[1] IS NULL THEN c.[2] ELSE c.[1] END as a, [3]
FROM [t] c
WHERE c.[5]='')
SELECT a, COUNT(DISTINCT c.[3]) 
FROM cte
GROUP BY a

Creating a common table expression (CTE) defines the column names as a reference you can use in a subsequent query.

2 Likes

Is there a reason not to use COALESCE(c.[1], c.[2]), which is simpler, instead of CASE?

1 Like

I could be mistaken but you may be missing the opportunity for a very high performance bit of "pre-aggregation". Tell me the datatypes for the columns you're using rom the "c" table and I'll build a wad of test data to see. If it turns out that I'm right, then I'll post the method I'm thinking of along with a comparison.

1 Like

If you really want better help - then you should ask your questions by supplying test data. I know you have been asked before...

Select t.a
     , count(distinct (c.[3])
  From t As c
 Cross Apply (Values (coalesce(c.[1], c.[2]))) As t(a)
 Where c.[5] = ''
 Group By t.a
1 Like

That's what I came up with except I used ISNULL() only because a long time back, I proved it was a little faster. I don't know if it still is or not.

Shifting gears, the reason I asked the Op for datatypes is because I believe there's a pre-aggregation optimization that might be had and I wanted the test data to simulate his data.

If the OP would provide test data for their questions - it would make it a whole lot simpler to provide an answer, as well as optimizing any provided solution. However, they seem reluctant to do that - for some reason.

It's ok.... I'm going to become reluctant to helping, in return. :mask:

Thanks but I cannot use my data as it is priorietary and any substitute may not capture all the parameters. I am rather asking for code ideas which I will then test in my real data and use appropriately. Thanks!

Post bogus non proprietary data. Like star wars characters. You cant come to a doctor and expect doctor to give you a cure without you telling doc your symptoms or where it hurts

Lordy,,, At least read the responses from the people that are trying to help you! I didn't ask you for your data. I asked you for the DATATYPES of your data.