how do I modify my having clause so my query will show the result that I want

Question

So currently my query shows result of all the "preferred" travel destinations of countries based on the continent, and I'm adding a having clause to show the continents that have more than 1 preferred destination. But as I soon as I add more columns I want to show, let's the say cities, my query returns nothing.

This is the query that work, it shows all continents that have more than 2 preferred countries as travel destination(the preferred flag is Y).

select  d.continent,d.country_name,di.is_preferred, count(1)
from continent c, countries ct
where c.cid = ct.id
and ct.preferred = Y
group by   d.continent,d.country_name,ct.is_preferred
having count(1) > 1
order by d.country_name asc;`

Let's say I want to have the same result showing, but I want to show all the list of cities of the countries that where continent have more than 1 preferred country. The cities column is from continent C table, as soon as I add to the having statement, the result is blank?

What did I do wrong?

Here is my modified query:

select  d.continent,d.country_name,di.is_preferred, c.cities, count(1)
from continent c, countries ct
where c.cid = ct.id
and ct.preferred = Y
group by   d.continent,d.country_name,ct.is_preferred, c.cities
having count(1) > 1
order by d.country_name asc;

I've tried modified my queries but got nothing in my result

Answer 1

I want to show all the list of cities of the countries that where continent have more than 1 preferred country.

If you GROUP BY continent, country_name then you are finding the pairing of continent and country where there are 2-or-more preferred entries; if you want it by continent only (as per the question) then you only want to GROUP BY continent.

If you have the sample data:

CREATE TABLE continent (cid, continent) AS
  SELECT 1, 'Africa'        FROM DUAL UNION ALL
  SELECT 2, 'Asia'          FROM DUAL UNION ALL
  SELECT 3, 'South America' FROM DUAL;

CREATE TABLE countries (id, country_name, is_preferred, cities) AS
  SELECT 1, 'Dominican Republic', 'Y', 'Kinshasa'  FROM DUAL UNION ALL
  SELECT 1, 'Nigeria',            'Y', 'Lagos'     FROM DUAL UNION ALL
  SELECT 1, 'Egypt',              'Y', 'Cairo'     FROM DUAL UNION ALL
  SELECT 2, 'China',              'N', 'Shanghai'  FROM DUAL UNION ALL
  SELECT 2, 'Pakistan',           'Y', 'Karachi'   FROM DUAL UNION ALL
  SELECT 3, 'Brazil',             'Y', 'Sao Paulo' FROM DUAL UNION ALL
  SELECT 3, 'Peru',               'Y', 'Lima'      FROM DUAL;

and you want to GROUP BY each continent and show a list of the preferred cities then only GROUP BY continent and use LISTAGG to aggregate the names of the cities into a delimited list:

SELECT c.continent,
       LISTAGG(ct.cities, ',') WITHIN GROUP (ORDER BY cities) AS cities
FROM   continent c
       INNER JOIN countries ct
       ON c.cid = ct.id
WHERE  ct.is_preferred = 'Y'
GROUP BY
       c.continent
HAVING COUNT(1) > 1
ORDER BY
       c.continent;

Which outputs:

CONTINENT	CITIES
Africa	Cairo,Kinshasa,Lagos
South America	Lima,Sao Paulo

fiddle