CODEWITHSUNDEEP
sqldistinct
MrVila
MrVila

Reputation: 93

count distincts including null

I have this problem and i cant resolve :

The table "habitacions" records, for each room, the time the guests must be awoken at (columns "hora" and "minut"). If the time is a null value, the guests must not be awoken. Give a SQL sentence returning the number of different hours (ignoring column "minut") someone must be awoken at. Willing not to be awoken is considered as a new different hour.

CREATE TABLE vigilants(
nom VARCHAR(20) PRIMARY key,
edat integer);

CREATE TABLE rondes(
hora INTEGER,
planta INTEGER,
vigilant VARCHAR(20) REFERENCES vigilants,
PRIMARY KEY(hora, planta));

CREATE TABLE habitacions(
num INTEGER,
planta INTEGER,
places INTEGER,
hora INTEGER,
minut INTEGER,
PRIMARY KEY(num, planta),
FOREIGN KEY(hora, planta) REFERENCES rondes);`

with this restrictions: Try to minimize the number of subqueries you need to solve the query. Furthermore, you are not allowed to use the following constructions: - SELECT in the FROM or SELECT. You are allowed to have subqueries (SELECT in the WHERE or HAVING) - Combinations of aggregation functions such as COUNT (COUNT. ..)), SUM (COUNT. ..)) and the like. - UNION if you can avoid it. - Non-standard functions (such as NVL) - CASE

example: with this inserts:

INSERT INTO vigilants(nom, edat) VALUES ('Mulder', 32);
INSERT INTO vigilants(nom, edat) VALUES ('Scully', 30);

INSERT INTO rondes(hora, planta, vigilant) VALUES (7, 1, 'Mulder');
INSERT INTO rondes(hora, planta, vigilant) VALUES (8, 1, 'Mulder');
INSERT INTO rondes(hora, planta, vigilant) VALUES (7, 2, 'Mulder');

INSERT INTO habitacions(num, planta, places, hora, minut) VALUES (1, 1, 1, 7, 30);
INSERT INTO habitacions(num, planta, places, hora, minut) VALUES (5, 1, 1, 7, 30);
INSERT INTO habitacions(num, planta, places, hora, minut) VALUES (2, 1, 1, 8, 30);
INSERT INTO habitacions(num, planta, places, hora, minut) VALUES (3, 1, 1, null, null);
INSERT INTO habitacions(num, planta, places, hora, minut) VALUES (4, 1, 1, null, null);
INSERT INTO habitacions(num, planta, places, hora, minut) VALUES (1, 2, 1, null, null);

result is 3 :) (7,8 and null)

Ty a lot

Upvotes: 0

Views: 277

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Hint: you can use the coalesce() function with count(distinct).

So, something like the following is allowed:

select count(distinct coalesce(hour, -1))

It replaces NULL values with the -1, which is not a valid value. This should have the effect that you are looking for.

Upvotes: 2

Related Questions