postgresql count distinct on differect condition

Question

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

1) Do you use mysql or postgresql? 2) add sample source data in text format and expected eventually result — Oto Shavadze
– Oto Shavadze, Commented Apr 10, 2017 at 9:37
count(distinct objectnaam) is not the total amount of unique visits to planets, but rather the total amount of unique planets (by name), which was being visited. -- Your tables' and columns' name are hard to read. Please translate them into english (either by replacing them, to provide a readable example), or give a translation/explanation below (in the question itself). — pozs
– pozs, Commented Apr 10, 2017 at 9:50
@pozs thank you for the explanation, i didn't see that. The question has been solved, but I'll definitely translate my future questions. — DreamsInHD
– DreamsInHD, Commented Apr 10, 2017 at 9:55

Vijunav Vastivch · Accepted Answer · 2017-04-10 09:43:06Z

1

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

answered Apr 10, 2017 at 9:43

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DreamsInHD Over a year ago

Yes thank you but is it possible without a subquery?

Vijunav Vastivch Over a year ago

According to your example no.. Otherwise you can do it using Stored Procedure if you want one line Query.

Vijunav Vastivch Over a year ago

If this one solved your problem then mark as answered

Collectives™ on Stack Overflow

postgresql count distinct on differect condition

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related