CODEWITHSUNDEEP
sqlgroup-bysubquery
Bertug
Bertug

Reputation: 1070

Invalid column name error in SQL sub-query with GROUP BY

SELECT 
    CepTel,
    (SELECT u
        top 1 sa1.SirketAdi 
    FROM
        SatisTum sa1 
    WHERE s.MusteriNo = sa1.MusteriNo) s1,
    (SELECT 
        top 1 sa2.FaturaSahibi 
    FROM
        SatisTum sa2 
    WHERE s.MusteriNo = sa2.MusteriNo) s2,
    (SELECT 
        top 1 sa3.Ad 
    FROM
        SatisTum sa3 
    WHERE s.MusteriNo = sa3.MusteriNo) s3,
    (SELECT 
        top 1 sa4.Soyad 
    FROM
        SatisTum sa4 
    WHERE s.MusteriNo = sa4.MusteriNo) s4,
    COUNT(DISTINCT MusteriNo) 
FROM
    SatisTum s 
GROUP BY CepTel,s1,s2,s3,s4 
HAVING COUNT(DISTINCT MusteriNo) > 1 
ORDER BY COUNT(DISTINCT MusteriNo)

Error:

Invalid column name 's1'.
Invalid column name 's2'.
Invalid column name 's3'.
Invalid column name 's4'.

Upvotes: 1

Views: 996

Answers (6)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

I think you are trying to get data from the same table SatisTum . You can simplify your query like below.

SELECT a.CepTel,t.SirketAdi,t.FaturaSahibi,t.Ad,t.Soyad,Count(Distinct a.MusteriNo) 
FROM SatisTum  a
  OUTER APPLY  (SELECT top 1 b.SirketAdi,b.FaturaSahibi,b.Ad,b.Soyad FROM SatisTum b WHERE a.MusteriNo = b.MusteriNo )t
GROUP BY CepTel,t.SirketAdi,t.FaturaSahibi,t.Ad,t.Soyad
HAVING Count(DISTINCT a.MusteriNo) > 1 
ORDER BY COUNT(DISTINCT a.MusteriNo);

Upvotes: 0

Jatin Patel
Jatin Patel

Reputation: 2104

Try this,

Select CepTel , 
    MusteriNo,
    MAX(s.SirketAdi)  s1,    -- you are getting random top 1, let it be the max or min!
    MAX(s.FaturaSahibi) s2 , 
    MAX(s.Ad)  s3, 
    MAX(s.Soyad) s4, 
    Count(Distinct MusteriNo) 
From SatisTum s 
group by  CepTel ,MusteriNo
having Count(Distinct MusteriNo) > 1 
order by COUNT(distinct MusteriNo)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your query is quite awkward. You have TOP 1, but there is no ORDER BY, meaning that the results are indeterminate.

However, you can solve the problem using OUTER APPLY:

Select CepTel, s1.SirketAdi, s2.FaturaSahibi, s3.Ad, s4.Soyad,
       Count(Distinct MusteriNo)
From SatisTum s outer apply
     (Select top 1 sa1.SirketAdi From SatisTum sa1 Where s.MusteriNo = sa1.MusteriNo
     ) s1 outer apply
     (Select top 1 sa2.FaturaSahibi From SatisTum sa2 Where s.MusteriNo = sa2.MusteriNo
     ) s2 outer apply
     (Select top 1 sa3.Ad From SatisTum sa3 where s.MusteriNo = sa3.MusteriNo
     ) s3 outer apply
     (Select top 1 sa4.Soyad  From SatisTum sa4 where s.MusteriNo = sa4.MusteriNo
     ) s4
group by  CepTel, s1.SirketAdi, s2.FaturaSahibi, s3.Ad, s4.Soyad
having Count(Distinct MusteriNo) > 1 
order by COUNT(distinct MusteriNo);

If this works but does not do what you want, then ask another question with sample data, desired results, and an explanation of what you are trying to calculate.

Upvotes: 2

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT T.CepTel
    ,T.s1
    ,T.s2
    ,T.s3
    ,T.s4
    ,Count(DISTINCT T.MusteriNo)
FROM (
    SELECT CepTel
        ,(
            SELECT TOP 1 sa1.SirketAdi
            FROM SatisTum sa1
            WHERE s.MusteriNo = sa1.MusteriNo
            ) s1
        ,(
            SELECT TOP 1 sa2.FaturaSahibi
            FROM SatisTum sa2
            WHERE s.MusteriNo = sa2.MusteriNo
            ) s2
        ,(
            SELECT TOP 1 sa3.Ad
            FROM SatisTum sa3
            WHERE s.MusteriNo = sa3.MusteriNo
            ) s3
        ,(
            SELECT TOP 1 sa4.Soyad
            FROM SatisTum sa4
            WHERE s.MusteriNo = sa4.MusteriNo
            ) s4
        ,MusteriNo
    FROM SatisTum s
    ) T
GROUP BY T.CepTel
    ,T.s1
    ,T.s2
    ,T.s3
    ,T.s4
HAVING Count(DISTINCT T.MusteriNo) > 1
ORDER BY COUNT(DISTINCT T.MusteriNo)

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

You cannot group by an alias.

Use the actual expressions

group by (Select top 1 sa1.SirketAdi From SatisTum sa1 Where s.MusteriNo = sa1.MusteriNo ),

etc

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

You have used alias in group by so it throw error:

group by CepTel , s1 , s2 , s3 , s4

Instead replace original column

Select CepTel , 
    (Select top 1 sa1.SirketAdi From SatisTum sa1 Where s.MusteriNo = sa1.MusteriNo ) s1, 
    (Select top 1 sa2.FaturaSahibi From SatisTum sa2 Where s.MusteriNo = sa2.MusteriNo ) s2 , 
    (Select top 1 sa3.Ad From SatisTum sa3 where s.MusteriNo = sa3.MusteriNo ) s3, 
    (Select top 1 sa4.Soyad From SatisTum sa4 where s.MusteriNo = sa4.MusteriNo) s4, 
    Count(Distinct MusteriNo) 
From SatisTum s 
group by CepTel , 
    (Select top 1 sa1.SirketAdi From SatisTum sa1 Where s.MusteriNo = sa1.MusteriNo ) , 
    (Select top 1 sa2.FaturaSahibi From SatisTum sa2 Where s.MusteriNo = sa2.MusteriNo ) ,
    (Select top 1 sa3.Ad From SatisTum sa3 where s.MusteriNo = sa3.MusteriNo ), 
    (Select top 1 sa4.Soyad From SatisTum sa4 where s.MusteriNo = sa4.MusteriNo)
having Count(Distinct MusteriNo) > 1 order by COUNT(distinct MusteriNo)

Upvotes: 1

Related Questions