I'm trying to write a query that

1. Labels null values as N/A
2. Only returns Country_Codes that appear more than once (including N/A values)
3. Orders alphabetically with N/A records at the top.

For #2, I wrote a subquery that counts which country_codes appear more than once but it is excluding null values which isn't giving me the right results. Does anyone know how I can alter my query to include the expected results?

Expected results(left) vs Actual results(right)

select ifnull(country_code,"N/A") as country_codes, continent_code
    from continent_map
    where country_code
    IN (
        SELECT country_code
        from continent_map
        group by country_code
        having count(country_code) > 1)
    
    order by field(country_codes,"N/A") DESC

CodePudding user response：

You must use IFNULL(country_code,"N/A") everywhere instead of single country_code:

SELECT IFNULL(country_code,"N/A") AS country_codes, 
       continent_code
FROM continent_map
WHERE IFNULL(country_code,"N/A") IN ( 
        SELECT IFNULL(country_code,"N/A")
        FROM continent_map
        GROUP BY country_code
        HAVING COUNT(IFNULL(country_code,"N/A")) > 1
)
ORDER BY FIELD(country_codes,"N/A") DESC

You must guarantee that "N/A" is absent in country_code column (or, if it is present, it is treated as the same value as NULL).

If "N/A" is really absent in country_code column then you may use HAVING COUNT(*) > 1 - but this shouldn't effect anything, including the performance.

CodePudding user response：

IN doesn't find NULLs. And COUNT(NULL) is 0 anyway. If you want to count rows use COUNT(*).

The easiest approach is probably to replace NULLs by N/A right away:

WITH countries AS
(
  SELECT COALESCE(country_code, 'N/A') AS country_code, continent_code
  FROM continent_map
)
SELECT country_code, continent_code
FROM countries
WHERE country_code
IN (
      SELECT country_code
      FROM countries
      GROUP BY country_code
      HAVING COUNT(*) > 1
    )
ORDER BY field(country_code, 'N/A') DESC, country_code, continent_code;

Here is the same thing shorter by using the window function COUNT OVER:

SELECT country_code, continent_code
FROM
(
  SELECT COALESCE(country_code, 'N/A') AS country_code,
  continent_code,
  COUNT(*) OVER (PARTITION BY country_code) as cnt
  FROM continent_map
) counted
WHERE cnt > 1
ORDER BY field(country_code, 'N/A') DESC, country_code, continent_code;

CodePudding user response：

You would edit the query to use count(*) as Tim as mentioned. Next to get the nulls on top we can order by as follows.

select ifnull(country_code,"N/A") as country_codes, continent_code
    from continent_map
    where ifnull(country_code,'-1')
    IN (
        SELECT ifnull(country_code,'-1')
        from continent_map
        group by country_code
        having count(*) > 1)
    order by country_code is null desc,country_code asc

db fiddle link https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e76c9d4b733ca83b00067e611dbd1e80