CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
nervousDev
nervousDev

Reputation: 75

SQL Procedure to get most repeated values

I have the following tables:

table Guy{
  ID integer PK,
  Country varchar(50)
}
table Activity{
  id_Act integer PK,
  Guy_ID FK,
  Date_Act date
}

They have a relation of one to may (guy to Activity). Also, this tables are made-up so sorry me if there are mistakes.

Now, I want to know wich years had the most activities done by guys from country "XPTO" using a procedure.

What is the best option here? (using Oracle SQL, PL SQL).

EDIT:

I started with this query to get the repetead values counted. After this step I don't know what to do.

 Select to_char(A.Date_Act, 'YYYY'), count(to_char(A.Date_Act, 'YYYY')) AS conter
 from GUY G, ACTIVITY A
 where G.ID = A.GUY_ID AND G.COUNTRY = 'XPTO'
 GROUP BY A.Date_Act;

Upvotes: 0

Views: 65

Answers (1)

Multisync
Multisync

Reputation: 8787

You need to group by the function:

Select to_char(A.Date_Act, 'YYYY'), count(A.id_Act) AS conter
from GUY G join ACTIVITY A on G.ID = A.GUY_ID
where G.COUNTRY = 'XPTO'
GROUP BY to_char(A.Date_Act, 'YYYY');

To get the years with the max number of activities you can use analytic functions:

select yr, conter from
    (Select to_char(A.Date_Act, 'YYYY') AS yr, count(A.id_Act) AS conter,
           rank() over (order by count(A.id_Act) desc) rnk
    from GUY G join ACTIVITY A on G.ID = A.GUY_ID
    where G.COUNTRY = 'XPTO'
    GROUP BY to_char(A.Date_Act, 'YYYY')
    ) 
where rnk=1;

Or ROW_NUMBER instead of RANK if you need only one row.

Upvotes: 1

Related Questions