question

yyhh_2000 avatar image
yyhh_2000 asked

Column invalid

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.


columninvalid-character
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, this is exactly the rule I am looking for. Thank you very much.
0 Likes 0 ·

1 Answer

·
anthony.green avatar image
anthony.green answered
What’s the full query your running?

Group/having are processed before the select so at the having point it has no clue about the alias challenge_counter, the having will need to be the aggregate count(challenge_id) instead.





10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.