CODEWITHSUNDEEP
sqlsql-serversubqueryaliasscalar
Jess
Jess

Reputation: 57

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)

Upvotes: 2

Views: 207

Answers (2)

Parfait
Parfait

Reputation: 107587

Consider also forgoing the use of subquery and sum the logical condition for counts since True resolves as 1 and False as 0.

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

In SQL Server, you may need to wrap in case statement

create table matched_pairs as
select
     count(abc_accno) as Count_abc_AccNo
    , count(xyz_accno) as Count_xyz_AccNo
    , (case when abc_accno = xyz_accno then 1 else 0 end)
from t1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 6

Related Questions