Solution written in PostgreSQL 9.4 (Compatability) Given a table events with the

Question

Solution written in PostgreSQL 9.4 (Compatability)

Given a table events with the following structure create table events ( sensor id integer not null, event type integer not null, value float not null, time timestamp not null write an SQL query that returns a set of all sensors (sensor_id) with the number of different event types (event type) registered by each of them, ordered by sensor id (in ascending order) For example, given the following data sensor id Ievent type value | time 2 2 2 3 2 1 3.45 I -149.42 I 4.12 1 3.47 | 24.54 | 2014-02-13 12:42:00 | 2014-02-13 12:42:00 | 2014-02-13 12:54 39 | 2014-02-13 12:42:00 | 2014-02-13 13:32:36 your query should return the following rowset sensor id types 2 3 The names of the columns in the rowset don't matter, but their order does

Explanation / Answer

Answer:

SELECT res.sensor_id, count(res.event_type) as types
FROM (SELECT DISTINCT sensor_id, event_type
FROM events) as res
GROUP BY res.sensor_id
ORDER BY res.sensor_id

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.