Search code examples
sqlsql-serversubqueryaliasscalar

Create Alias in Scalar Subquery - SQLServer

I am doing this in SAS, however the principles should apply from sqlserver. I have a table that is the result of a full join on account number, so it looks like this:

abc_accno  xyz_accno
11111      .
22222      22222
.          66666
33333      33333
44444      44444
.          55555

My end goal is to have a table that looks like this:

count_abc_accno  count_xyz_accno  matched_pairs
4                5                3

So my query is this:

create table matched_pairs as
select
     count(abc_accno) as Count_abc_AccNo
    ,count(xyz_accno) as Count_xyz_AccNo
    ,(select count(abc_accno) as matched_pairs 
        from t1
        where abc_accno = xyz_accno)
from t1
;

What I am ending up with is this:

count_abc_accno  count_xyz_accno  _randomname
4                5                3

I am thinking there is probably a more elegant way to do this, but we work with the tools (and knowledge) we have. I want the matched pairs variable to say matched pairs, but am not able to figure out how to get it off of the system generated name.

What would be the best way to go about doing this? Is there a more elegant solution than what I am trying? (there always seems to be...one day)

Solution

  • Use as to assign a column alias:

    create table matched_pairs as
    select count(abc_accno) as Count_abc_AccNo,
           count(xyz_accno) as Count_xyz_AccNo,
           (select count(abc_accno)
            from t1
            where abc_accno = xyz_accno
           ) as matched_pairs 
    from t1;

    The alias in the subquery has no relevance in the outer query. The subquery simply returns a value. You have to assign the alias in the outer query.