How to select company which have two groups

Question

I still tried select all customers which is in two group. Duplicate from customers is normal because select is from invoice but I need to know the customers who had a group in the first half year and jumped to another in the second half year.

Example:

SELECT
  f.eankod as kod,                                              --(groups)
  ad.kod as firma,                                              --(markComp)
  f.nazfirmy as nazev,                                          --(nameComp)
  COUNT(ad.kod),
  sum(f.sumZklZakl + f.sumZklSniz + f.sumOsv) as cena_bez_dph   --(Price)
FROM
  ddoklfak as f
LEFT OUTER JOIN aadresar ad ON ad.idfirmy = f.idfirmy 
WHERE
  f.datvyst >= '2017-01-01'
  and f.datvyst <= '2017-12-31'
  and f.modul like 'FAV'
GROUP BY
  f.eankod,
  ad.kod,
  f.nazfirmy
HAVING COUNT (ad.kod) > 1
order by
  ad.kod

Result:

 GROUP   markcomp  nameComp    price
| D002 | B5846 | Cosmopolis | price ... |
| D003 | B6987 | Tismotis   | price ... |
| D009 | B8974 | Teramis    | price ... |
| D006 | B8876 | Kesmethis  | price ... | I need this, same company but diferent group, because this 
| D008 | B8876 | Kesmethis  | price ... | company jumped. I need know only jumped company. (last two rows from examples)

Thx for help.

Answer 1

You can use a CTE to find out which nameComp show up multiple times, and keep those ones only. For example:

with
x as (
  -- your query
)
select * from x where nameComp in (
  select nameComp from x group by nameComp having count(*) > 1
)