T-SQL subquery does not accept Alias

Question

I am using SSMS 2019 and Report builder to make reports, MY problem is in this query it does not accept AS

SELECT Country,ServiceGrade as BestCenter from VW_FactCallCenter
         WHERE EXISTS(SELECT DISTINCT Country, MAX(ServiceGrade) as MaxGrade FROM VW_FactCallCenter GROUP BY Country) AS BestCenter

With this Query I get my desired Result, But when I add subquery to it, I cant use 2 column, when I use With Exists then I cant use Alias

SELECT DISTINCT Country, MAX(ServiceGrade) as MaxGrade FROM VW_FactCallCenter GROUP BY Country

Desired Result

This is my DDL

CREATE view VW_FactCallCenter (IssuesRaised Smallint, 
                            LevelOneOperators Smallint,
                            LevelTwoOperators Smallint, 
                            ServiceGrade Float, 
                            Shift Nvarchar(20),
                            Date Datetime,
                            TotalOperators Smallint,
                            Country Varchar(7) )


INSERT into VW_FactCallCenter (IssuesRaised, 
                                LevelOneOperators ,
                                LevelTwoOperators , 
                                ServiceGrade , 
                                Shift,
                                Date ,
                                TotalOperators,
                                Country ) VALUES (1,1,4,0.12,'midnight','2014-05-05',5,'Italy');
                                

INSERT into VW_FactCallCenter (IssuesRaised, 
                                LevelOneOperators ,
                                LevelTwoOperators , 
                                ServiceGrade , 
                                Shift,
                                Date ,
                                TotalOperators,
                                Country ) VALUES (2,2,7,0.18,'AM','2014-08-12',9,'France');

INSERT into VW_FactCallCenter (IssuesRaised, 
                                LevelOneOperators ,
                                LevelTwoOperators , 
                                ServiceGrade , 
                                Shift,
                                Date ,
                                TotalOperators,
                                Country ) VALUES (4,5,7,0.21,'AM','2014-09-12',12,'Germany');

My goal is to compare Call Centers depending on Country as you can see in the Desired Result image, I am thinking to use Concat but without using that is there a way to do it? Unfortunately I have been asking this problem to my supervisor but he seems so busy so I need help, I have been trying to solve this problem since yesterday I have tried other methods but I really want to use Alias for a report in my subquery

The terms inside a `WHERE` clause are used to filter the result set, there would be no point in assigning aliases to them. — Álvaro González, Dec 10 '21 at 10:25
The exists subquery does not take an alias, and also your intended logic is not clear. — Tim Biegeleisen, Dec 10 '21 at 10:26
You do not use an alias with *exists*, you don't make any correlation with your *exists*, it's not clear what you are trying to do. — Stu, Dec 10 '21 at 10:28
@TimBiegeleisen as I see I should use concat, if I dont use Where Exists then in subquery it only accept 1 column, I tried where exists to see 2 column but I will merge that 2 column to be more clear, I have described my intended logic, its simple, to compare callcenters that between countries, I am new to this, I have too much to learn, could you please describe me what you dont get ? — HamzaB, Dec 10 '21 at 10:30
@Stu ok thank you for informing me, I will try to use concat, to merge 2 column as 1 is this a correct approach? I am trying to compare countries that has call centers inside of it, maxgrade is for grading them, there are various centers inside of countries but I just want to see the most sucessful (grades that is higher than 0.10 is good) in the image Italy has the best center, if it makes clear I am using AdventureWorksDW2019 as sample DB — HamzaB, Dec 10 '21 at 10:36
I have no idea what it is you are trying to do, you have not explained your data or how you get to your desired results. For a start your image has 3 rows, your data is only two. — Stu, Dec 10 '21 at 10:42
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Dec 10 '21 at 13:43
Side point: `DISTINCT` is completely bogus here because you have a `GROUP BY` — Charlieface, Dec 10 '21 at 13:44

score 0 · Accepted Answer · answered Dec 10 '21 at 11:15

If I understand correctly you can find greatest service grade per country like so:

SELECT Country, ServiceGrade
FROM VW_FactCallCenter AS t1
WHERE ServiceGrade = (
    SELECT MAX(ServiceGrade)
    FROM VW_FactCallCenter AS t2
    WHERE t2.Country = t1.Country
)

T-SQL subquery does not accept Alias

1 Answers1