CODEWITHSUNDEEP
sqlpostgresqlgroup-by
DreamsInHD
DreamsInHD

Reputation: 413

postgresql count distinct on differect condition

I'm stuck on an exercise where I need to count the total amount of unique visits to planets, but if the planet is the moon (maan), then it should be counted twice. Also the client number should be 121

select case 
        when objectnaam = 'Maan' then count(objectnaam)
        else count(distinct objectnaam)
   end as aantal_bezoeken
from klanten inner join deelnames on klanten.klantnr = deelnames.klantnr
         inner join reizen on deelnames.reisnr = reizen.reisnr
         inner join bezoeken on reizen.reisnr = bezoeken.reisnr
where klanten.klantnr = 121
group by objectnaam

And it gives me this result

aantal_bezoeken
      1
      4
      1
      1

but the result should be 

aantal_bezoeken
      7

I just need to add all these values together but I don't know how to, or maybe there's a better more simple solution. It should be without subqueries

Upvotes: 0

Views: 365

Answers (1)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

Try this:

select sum(aantal_bezoeken) as aantal_bezoeken from
(select case 
        when objectnaam = 'Maan' then count(objectnaam)
        else count(distinct objectnaam)
   end as aantal_bezoeken
from klanten inner join deelnames on klanten.klantnr = deelnames.klantnr
         inner join reizen on deelnames.reisnr = reizen.reisnr
         inner join bezoeken on reizen.reisnr = bezoeken.reisnr
where klanten.klantnr = 121
group by objectnaam) as a

Upvotes: 1

Related Questions