'how to find difference between no_of_value and no_of_distinct columns values?

Let be the number of CITY entries in STATION, and let be the number of distinct CITY names in STATION; query the value of from STATION. In other words, find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

Input Format

The STATION table is described as follows: enter image description here

where LAT_N is the northern latitude and LONG_W is the western longitude.

sqloracle


Solution 1:[1]

Use distinct in count function. 

select count(city) - count(distinct city) 
from station

Solution 2:[2]

SELECT count(city) - count(DISTINCT city) FROM station;

Do not forget to add semicolon ';' after the query

Solution 3:[3]

You could use having for filtering the resul on aggregated function 

 select city,  count(*), count(distinct city) 
 from station
 group by city
 having count(*) <> count(distinct city)

Solution 4:[4]

If I understand correctly:

select count(city) - count(distinct city)
from station;

You would do this to get the number of duplicated values in the table. I might be more interested in the list of cities and the number of duplicates:

select city, count(*) - 1 as numdups
from station
group by city
having count(*) > 1;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Adam Silenko
Solution 2 Arpit kumar jain
Solution 3 ScaisEdge
Solution 4 Gordon Linoff

Related Questions