Oracle - Data with one id in one line-CodePudding

New to sql, trying to create a query for reporting.

The following table is available:

Number	NO	Date
111	332	14.11.22
111	331	13.11.22
111	330	13.11.22
122	312	12.11.22
122	311	11.11.22
122	310	11.11.22

I would like to come to this view - A row in one row had a number, a maximum and a minimum NO and the corresponding dates and quantities associated with it:

Number	NO min	Date min	NO max	Date max	Count
111	330	13.11.22	332	14.11.22	3
122	310	11.11.22	312	12.11.22	3

CodePudding user response：

  with t(Num,   Start_number, Date_start) as (
select 111 , 225 , '11.11.22' from dual union all
select 111 , 223 , '9.11.22' from dual union all
select 111 , 220 , '9.11.22' from dual union all
select 222 , 347 , '11.11.22' from dual union all
select 222 , 345 , '11.11.22' from dual union all
select 222 , 343 , '10.11.22' from dual
)
select 
  Num,
  min(Start_number) as minStart,
  max(Start_number) as maxStart,
  min(Date_start) as minDate,
  max(Date_start) as maxDate,
  Count(Start_number) as cnt
from t
group by Num
order by minDate