oracle - SQL query to get group by

Question

I have theses tables. I want to get the number of installations by agency that have at least one reading. How do I get this?

Thanks.

repository

Column
id
number

question from:https://stackoverflow.com/questions/65952186/sql-query-to-get-group-by

Answer 1

You can use group by as follows:

select i.agency, count(distinct m.id) meters
  from installation i 
  join meter m on m.installation_id = i.id
  join repository rp on rp.id = m.repository_id
  join reading r on r.repository_number = rp.number
 group by i.agency;