Why is the column 'challenge_counter' invalid?

Transact-SQL
1

Here is the query:
/1st SELECTING THE VALUES requested to be printed/
SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS challenge_counter
FROM hackers h
JOIN challenges c
ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name

/2nd applying the values found before/

HAVING challenge_counter IN (
SELECT aux_table.counter
FROM(
SELECT hacker_id, COUNT(challenge_id) AS counter
FROM challenges
GROUP BY hacker_id
) AS aux_table
GROUP BY aux_table.counter
HAVING COUNT(aux_table.counter) = 1
)
OR
challenge_counter =(
SELECT MAX(aux_table.counter)
FROM(
SELECT hacker_id, COUNT(challenge_id) AS counter
FROM challenges
GROUP BY hacker_id
) AS aux_table)
/* Finally we order as requested (by counter and hacker_id)*/
ORDER BY challenge_counter DESC, h.hacker_id ASC;

Getting error like:

Msg 207, Level 16, State 1, Server dbrank-tsql, Line 16

Invalid column name 'challenge_counter'.

Msg 207, Level 16, State 1, Server dbrank-tsql, Line 27

Invalid column name 'challenge_counter'.

I did not figure out how would the column 'challenge_counter' be invalid? Please point it out.

2

the below part of the query does not understand challenge_counter

one solution is

select 
    name
 , count(*) as count_name 
 into
     #temp_table
from 
     table_xyz

select * from #temp_table where count_name > 10

3

You can't use the alias, you need to use the original expression:

...
HAVING COUNT(c.challenge_id) IN (
...
OR
COUNT(c.challenge_id) =(
...

4

So here you used two queries. I guess the alias can not be used in this same query. But in what situation an alias can or can not be used in the same query. Any rules for it?

5

Yes this do work. I would like to know why the alias can not be used here. Any ideas? thanks a lot.

6

SQL Server just doesn't allow it.

You can only use an alias created in that same query if you can use a CROSS APPLY to create the alias. Then you can use the alias throughout the query.

1 Like
7 
;with trojan
as
(
SELECT h.hacker_id, 
 h.name, 
COUNT(c.challenge_id) AS challenge_counter
FROM hackers h
JOIN challenges c
ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
)
select *
 from trojan
WHERE challenge_counter IN (
		SELECT aux_table.counter
		FROM(
		SELECT hacker_id, COUNT(challenge_id) AS counter
		FROM challenges
		GROUP BY hacker_id
		) AS aux_table
		GROUP BY aux_table.counter
		HAVING COUNT(aux_table.counter) = 1
)
OR
challenge_counter =(
	SELECT MAX(aux_table.counter)
	FROM(
	SELECT hacker_id, COUNT(challenge_id) AS counter
	FROM challenges
	GROUP BY hacker_id
	) AS aux_table
)
/* Finally we order as requested (by counter and hacker_id)*/
ORDER BY challenge_counter DESC, hacker_id ASC;
1 Like
8
9

Good idea!

10

It would be WHERE in that case and not HAVING.

1 Like