Skip to content
Advertisement

SQL: Retrieve missing values from superset

I have a table like this:

Device  | Rating
-----------------
iPhone  | 1
iPhone  | 2
iPhone  | 4
iPhone  | 5
Android | 3
Android | 5

I want to return a table with all the ratings possible. In this case 1 to 5. So my resulting table will be:

Device  | Rating
-----------------
iPhone  | 1
iPhone  | 2
iPhone  | 3
iPhone  | 4
iPhone  | 5
Android | 1
Android | 2
Android | 3
Android | 4
Android | 5

I have tried using something like:

SELECT a.device, b.rating
FROM device_rating_table a
RIGHT JOIN rating_lookup_table b
ON a.rating = b.rating
GROUP BY 1,2
;

but this does not work. Can you please suggest a solution for this?

Advertisement

Answer

You can cross join the list of distinct devices with numbers between 1 and 5:

select d.device, r.rating
from (select distinct device from mytable) d
cross join generate_series(1, 5) as r(rating)

Or if you want to generate the rating range from the minium and maximum values that are in the table:

select d.device, r.rating
from (select distinct device from mytable) d
cross join (select generate_series(min(rating), max(rating)) from mytable) as r(rating)

Finally: if all the ratings are availabel in the table already, no need to use generate_series():

select d.device, r.rating
from (select distinct device from mytable) d
cross join (select distinct rating from mytable) r
3 People found this is helpful
Advertisement