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

[sqlor]

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!

[robert_volk]

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.

[Fred_unique]

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

[Ifor]

Is this statement correct?

You need to understand the logical order of query processing. eg: With your examples, if Cancelled is in Table2 (and you should really have written Table2.Cancelled or used an alias) then your second query will effectively convert the LEFT OUTER JOIN to an INNER JOIN as the NULLs from the LEFT OUTER JOIN will never be equal to 'Y'. Create some test data and see what happens.

[JeffModen]

@sqlor

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!

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.

[jeffw8713]

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

[JeffModen]

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.

[jeffw8713]

@JeffModen

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.

[JeffModen]

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

[sqlor]

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!

[yosiasz]

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

[JeffModen]

@sqlor

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!

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.