title: SQL count DISTINCT ONCE user_id multiple attributes
body: <p>Hello there I cant manage to get a good result for the following case:</p>
  
  <p>I have a table which is like this:</p>
  
  <pre><code>UserID | Label
  -------- ------
  1      | Private
  1      | Public
  2      | Private
  3      | Hidden
  4      | Public
  5      | Hidden
  </code></pre>
  
  <p>I want to have the following happening if a User has following assigned he is:</p>
  
  <p>Private and Hidden are treaten the same: lets say Business<br>
  Public: BtoC<br>
  Public and Private and/or Hidden: both<br></p>
  
  <p>So in the end I have a <code>count(DISTINCT UserID)</code> of </p>
  
  <pre><code>Business         3
  BtoC             1
  both             1
  </code></pre>
  
  <p>I have tried to use <code>CASE WHEN</code> but it doesn't work my current total query looks like this:</p>
  
  <pre><code>SELECT gen_month,
         count(DISTINCT cu.id) as leads,
          a.label
  FROM generate_series(DATE_TRUNC('month', CURRENT_DATE::date - 96*INTERVAL '1 month'), CURRENT_DATE::date, '1 month') m(gen_month)
  LEFT OUTER JOIN company_user AS cu
    ON (date_trunc('month', cu.creation_date) = date_trunc('month', gen_month))
  LEFT JOIN user u
    ON u.user_id = cu.id
  LEFT join user_account_status as uas
  on cu.id = uas.user_id
  LEFT JOIN account as a
  on uas.account_id = a.id
  where gen_month &gt;= DATE_TRUNC('month',NOW() - INTERVAL '5 months')
  group by  m.gen_month, a.label
  order by gen_month
  </code></pre>
  
  <p>So my main problem now is that the count appears in every attribute once.
  How can I make a userid only count once under condition CASE WHEN user_id appears Public and (Private or Hidden) THEN count(DISTINCT user_id) as Both?</p>
  
  <p>Addition: its mySQL mariaDB and postgreSQL. But first I would happy with Postgres</p>