Problem with SQL query,highest amount of something-CodePudding

Hi i have this problem where i need to do this:

-For each trainer who has given only Yoga lessons, show the name, the surname, and the city of the gym where he has given the highest number of lessons.

From this table:

TRAINER (SSN*, NameT, Surname, City)

GYM (CodG*, NameG, Address, City)

SPECIALTY (CodS*, NameS, Description)

LESSON (SSN*, CodG*, Date*, CodS, ParticipantsNumber)

Asterisks are the primary keys. what i have done for now is this:

SELECT T.Name,T.Surname,G.City
FROM TRAINER AS T,LESSON AS L,SPECIALTY AS S,GYM AS G
WHERE T.SSN=L.SSN AND S.CodS=L.CodS AND G.CodG=L.CodG
AND T.SSN NOT IN(/*SSN of instructor who have given at least one lesson other than Yoga*/
                    SELECT T1.SSN
                    FROM TRAINER AS T1,LESSON AS L1,SPECIALTY AS S1
                    WHERE T1.SSN=L1.SSN AND S1.CodS=L1.CodS AND S1.NameS<>'Yoga')

What I really can't do is the part of the gym with the highest amount of lessons, since from what i understood MAX(COUNT *) is not doable, maybe this could be done with correlations between queries but i don't see how.

Any help would be much aprecciated,thanks in advance!

P.S i mainly use APEX ORACLE

CodePudding user response：

Start by adding count(1) as num_times to the original query , then use row_number() to find out the rank, then filter out only those with _rank=1

with data as (
SELECT T.Name,T.Surname,G.City, count(1) as num_times
FROM TRAINER AS T,LESSON AS L,SPECIALTY AS S,GYM AS G
WHERE T.SSN=L.SSN AND S.CodS=L.CodS AND G.CodG=L.CodG
AND T.SSN NOT IN(/*SSN of instructor who have given at least one lesson other than Yoga*/
                    SELECT T1.SSN
                    FROM TRAINER AS T1,LESSON AS L1,SPECIALTY AS S1
                    WHERE T1.SSN=L1.SSN AND S1.CodS=L1.CodS AND S1.NameS<>'Yoga'),
first_pass as (
group by 1,2,3)
select
*, row_number() over (partition by name, surname order by num_times desc) as _rank
)
select * from first_pass where _rank = 1

CodePudding user response：

For each person/gym combination, you can use a aggregation to count the classes and use conditional aggregation to count if any were not yoga; then, avoiding querying the tables twice, you can use analytic functions to rank the gyms and total the number of non-yoga lessons; and finally, in the outer query, can filter for only those highest-ranked lessons where there were no non-yoga lessons:

SELECT namet, surname, city
FROM   (
  SELECT l.ssn,
         MAX(namet) AS namet,
         MAX(surname) AS surname,
         g.codg,
         MAX(city) AS city,
         SUM(COUNT(CASE WHEN s.NameS  <> 'Yoga' THEN 1 END))
           OVER (PARTITION BY l.ssn)
           AS num_other_lessons,
         RANK() OVER (PARTITION BY l.ssn ORDER BY COUNT(*) DESC) AS gym_rank
  FROM   lesson l
         INNER JOIN trainer t   ON (l.ssn = t.ssn)
         INNER JOIN gym g       ON (l.codg = g.codg)
         INNER JOIN specialty s ON (l.cods = s.cods)
  GROUP BY
         l.ssn,
         g.codg
)
WHERE  num_other_lessons = 0
AND    gym_rank = 1

Which, for the sample data:

CREATE TABLE TRAINER (SSN PRIMARY KEY, NameT, Surname) AS
SELECT 1, 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Baron' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Count' FROM DUAL;

CREATE TABLE GYM (CodG PRIMARY KEY, NameG, City) AS
SELECT 10, 'GymA', 'Adelaide' FROM DUAL UNION ALL
SELECT 11, 'GymB', 'Berlin'   FROM DUAL UNION ALL
SELECT 12, 'GymC', 'Caracas'  FROM DUAL UNION ALL
SELECT 13, 'GymD', 'Dubai'    FROM DUAL;

CREATE TABLE SPECIALTY (CodS PRIMARY KEY, NameS, Description) AS
SELECT 20, 'Yoga',      'Descr1' FROM DUAL UNION ALL
SELECT 21, 'Not Yoga',  'Descr2' FROM DUAL;

CREATE TABLE LESSON (SSN, CodG, "DATE", CodS, ParticipantsNumber,
  PRIMARY KEY (SSN, CodG, "DATE")
) AS
SELECT 1, 10, DATE '1970-01-01'   LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT 1, 11, DATE '1970-01-01'   LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 2, 10, DATE '1970-01-01'   LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 2, 12, DATE '1970-01-01'   LEVEL - 1, 21, 1 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 3, 10, DATE '1970-01-01'   LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 3, 13, DATE '1970-01-01'   LEVEL - 1, 20, 1 FROM DUAL CONNECT BY LEVEL <= 3;

Outputs:

NAMET SURNAME CITY

Alice Abbot Adelaide

Carol Count Dubai

NAMET	SURNAME	CITY
Alice	Abbot	Adelaide
Carol	Count	Dubai

db<>fiddle here

CodePudding user response：

I would do something like this:

select t.namet, t.surname, g.city
from   (
  select ssn, min(codg) keep (dense_rank last order by cnt) as codg
  from   (
    select ssn, codg, count(*) as cnt
    from   lesson
    where  ssn in (
             select ssn
             from   trainer
             where  ssn not in (
               select l.ssn
               from   lesson l join specialty s on l.cods = s.cods
               where  s.names <> 'Yoga'
             )
           )
    group  by ssn, codg
  )
  group  by ssn
) sq
       join trainer t on t.ssn  = sq.ssn
       join gym     g on g.codg = sq.codg
;

The heavy lifting is done in the subquery sq - finding the ssn of trainers who only teach Yoga, then for each such teacher counting lessons at each gym and selecting only the gym with the highest number of lessons (the gym with the lowest code, in case of ties). Note the aggregate function last used in the select list: min(codg) keep (dense_rank LAST order by cnt) This is a very useful aggregate function that many programmers apparently never learn about.

Then the rest is a trivial join to the trainers and gyms tables.

Note - while I didn't use the with clause (perhaps they didn't teach it yet), I still chose to use modern, ANSI join syntax, rather than what they seem to teach you. You may want to ask your instructors: (1) why they are using the old Oracle join syntax, which even Oracle themselves recommend against; and (2) check the column name DATE in your last table. That is a reserved keyword; it can't be used as a column name. Your instructor should know better.