CODEWITHSUNDEEP
sqldb2subquery
Many_question
Many_question

Reputation: 95

How to use sub-queries in select clause with sub-queries in from clause

I try to query but got error tt.comp is not valid in context.

I think sub-queries#1(select clause) can't access a sub-queries#2(from clause) or sub-queries#1 execute before sub-queries#2.

SELECT grp1
      ,(select count(distinct tt.comp) as comp
        from t as tt
        where tt.lamp = 1
        and tt.pop = t.pop)
FROM (select ......) as t group by grp1

How to resolve that.

Thanks in advance.

Upvotes: 0

Views: 96

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Why you are referring the same table in sub-query, You can simply use CASE statement I guess -

 SELECT grp1,
        COUNT(DISTINCT CASE WHEN lamp = 1 THEN tt.comp END) AS COMP
FROM (select ......) as t
GROUP BY grp1

Upvotes: 2

Related Questions