CODEWITHSUNDEEP
sqlfirebird
Michael Müller
Michael Müller

Reputation: 401

select count () how to add column

This request gives me the count of the request occurrences where EMAIL occures.

    select count(*) 
    from ADRESS K left outer join ADRESS L 
       on K.LFDNRSECONDADRESS=L.LFDNR 
    left outer join ADRESS V 
       on K.VERLFDNR=V.LFDNR 
    where ((UPPER(K.EMAIL)= '[email protected]' 
       or exists (select ADRESSEMAILADR.LFDNR 
                  from ADRESSEMAILADR 
                  where ADRESSEMAILADR.ADRESSLFDNR=K.LFDNR 
                     and UPPER(ADRESSEMAILADR.EMAIL)= 
                                '[email protected]' )
    )) and K.ART='K'

But I also would like go get all occurrences of the column "LFDNR".

Like 

3 
1234 
2345 
3456
...

So the first is the count and the followingup are the results of all columns where LFDNR = X.

Of cause I tried 

LFDNR, select count(*)  
K.LFDNR, select count(*)

And so on... No luck so far.

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If I understand correctly, you want group by:

select k.LFDNR, count(*) 
from ADRESS K left outer join
     ADRESS L 
     on K.LFDNRSECONDADRESS = L.LFDNR left outer join
     ADRESS V 
    on K.VERLFDNR = V.LFDNR 
 where (UPPER(K.EMAIL)= '[email protected]' or
        exists (select ADRESSEMAILADR.LFDNR 
                from ADRESSEMAILADR 
                where ADRESSEMAILADR.ADRESSLFDNR = K.LFDNR and
                      UPPER(ADRESSEMAILADR.EMAIL) = '[email protected]'
               )
       ) and
       K.ART = 'K'
 group by k.LFDNR;

Upvotes: 2

Related Questions