6 Replies Latest reply on Feb 24, 2013 3:36 PM by user260user

    max, sum function

    user260user
      I would like to get this result:

      NAZWAWYDZIALU NAZWISKO
      ---------------- ---------------
      Organizacji i Zarządzania | Moskowitz
      Elektrotechniki Informatyki | Crocitto
      Mechaniczny | Walter
      Inżynierii Procesowej | Mierzwa
      Fizyki Technicznej | Olvsade

      August displayed the names of employees who have the most papers.

      I wrote the following query:

      SELECT w.NazwaWydzialu, p.nazwisko
      FROM wydzialy w,
                pracownicy p ,
                referatyPracownikow rp,
                referaty r,
                konferencje k
      WHERE rp.idreferat = r.idReferatu and
                     p.idPracownika = rp.idPracownika and
                     r.idKonferencji = k.idKonferencji
      HAVING count( * *)* = ( SELECT MAX ( sum (r.idReferatu))
                                    FROM wydzialy w,
                                         pracownicy p,
                                         referatyPracownikow rp,
                                         referaty r,
                                         konferencje k
                                    WHERE rp.idreferat = r.idReferatu and
                                              p.idPracownika = rp.idPracownika and
                                              r.idKonferencji = k.idKonferencji      
                                    GROUP BY r.idReferatu)
           GROUP BY w.NazwaWydzialu, p.nazwisko ;

      returns 0 rows :(

      Please kindly information, whether well-written query.

      Tables:

      CREATE TABLE publikacje (
           idPublikacji number(4) CONSTRAINT kl_glowny_pub PRIMARY KEY ,
           tytulPublikacji varchar(30),
           nazwaWydawnictwa varchar(30),
           dataPublikacji date
      );

      CREATE TABLE konferencje (
           idKonferencji number(4) CONSTRAINT kl_glowny_konf PRIMARY KEY ,
           NazwaKonferencji varchar(30),
           lokalizacja varchar(30),
           dataPoczatkowa date,
           dataZakonczenia date
      );

      CREATE TABLE przedmioty (
           idPrzedmiotu number(4) CONSTRAINT kl_glowny_przed PRIMARY KEY ,
           nazwaPrzedmiotu varchar(30)
      );

      CREATE TABLE FormyPrzedmiotow (
           idFormyPrzed number(4) CONSTRAINT kl_glowny_formyPrzed PRIMARY KEY,
           idPrzedmiotu CONSTRAINT kl_obcy_przed REFERENCES przedmioty(idPrzedmiotu),
           forma varchar(30),
           semestr number(1),
           rokAkademicki date
      );

      CREATE TABLE uczelnie (
           iduczelni number(4) CONSTRAINT kl_glowny_ucze PRIMARY KEY ,
           NazwaUczelni varchar(30),
           adres varchar(30),
           kodpocztowy varchar(6),
           miasto varchar(30),     
           NrTelefonu number(10),
           NIP_ucz     number(10)
      );

      CREATE TABLE wydzialy (
           idWydzialu number(4) CONSTRAINT kl_glowny_wydz PRIMARY KEY ,
           NazwaWydzialu varchar(30),
           Iduczelni CONSTRAINT kl_obcy_ucz REFERENCES uczelnie(iduczelni)
      );

      CREATE TABLE katedry (
           idkatedry number(4) CONSTRAINT kl_glowny_kated PRIMARY KEY ,
           Nazwakatedry varchar(30),
           IdWydzialu CONSTRAINT kl_obcy_wydz REFERENCES wydzialy(idWydzialu)     
      );

      CREATE TABLE pracownicy (
           idPracownika number(4) CONSTRAINT kl_glowny_prac PRIMARY KEY ,
           nazwisko varchar(30),
           imie varchar(30),
           tytul_naukowy varchar(30),
           adres varchar(30),
           kodPoczatkowy varchar(6),
           miasto varchar(30),
           telefonDomowy number(10),
           dataUrodzenia date,
           ID_katedry CONSTRAINT kl_obcy_kated REFERENCES katedry(idkatedry)
      );

      CREATE TABLE referaty (
           idReferatu number(4) CONSTRAINT kl_glowny_ref PRIMARY KEY ,
           tytulReferatu varchar(30),
           dataReferatu date,
           idKonferencji CONSTRAINT kl_obcy_konf REFERENCES konferencje(idKonferencji)
      );

      CREATE TABLE referatyPracownikow (
           idreferat CONSTRAINT kl_obcy_ref REFERENCES referaty(idReferatu),
           idPracownika CONSTRAINT kl_obcy_prac REFERENCES pracownicy(idPracownika)
      );

      CREATE TABLE publikacjePracownikow (
           idPracownika CONSTRAINT kl_obcy_prac2 REFERENCES pracownicy(idPracownika),
           idPublikacji CONSTRAINT kl_obcy_pub REFERENCES publikacje(idPublikacji)
      );

      CREATE TABLE przedmiotyProwadzone (
           idFormyPrzed CONSTRAINT kl_obcy_formyPrzed2 REFERENCES FormyPrzedmiotow(idFormyPrzed),
           idPracownika CONSTRAINT kl_obcy_prac3 REFERENCES pracownicy(idPracownika),
           LiczbaGodz number(10)
      );
        • 1. Re: max, sum function
          Rahul_India
          you need post some insert queries too.
          • 2. Re: max, sum function
            user260user
            INSERT INTO publikacje VALUES (1, 'tytulPublikacji 1', 'nazwaWydawnictwa 1',TO_DATE('29-APR-2011 20:17:26','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO publikacje VALUES (2, 'tytulPublikacji 2', 'nazwaWydawnictwa 2',TO_DATE('25-MAR-2012 22:15:06','DD-MON-YYYY HH24:MI:SS'));
            INSERT INTO publikacje VALUES (3, 'tytulPublikacji 3', 'nazwaWydawnictwa 3',TO_DATE('11-JAN-2008 09:14:08','DD-MON-YYYY HH24:MI:SS'));
            INSERT INTO publikacje VALUES (4, 'tytulPublikacji 4', 'nazwaWydawnictwa 4',TO_DATE('09-FEB-2009 10:14:13','DD-MON-YYYY HH24:MI:SS'));
            INSERT INTO publikacje VALUES (5, 'tytulPublikacji 5', 'nazwaWydawnictwa 5',TO_DATE('23-MAY-2010 17:16:23','DD-MON-YYYY HH24:MI:SS'));

            INSERT INTO konferencje VALUES (1, 'konferencje 1', 'Warszawa' ,TO_DATE('29-APR-2011 09:17:26','DD-MON-YYYY HH24:MI:SS') ,TO_DATE('29-APR-2011 20:17:26','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO konferencje VALUES (2, 'konferencje 2', 'Warszawa' ,TO_DATE('25-MAR-2012 10:15:06','DD-MON-YYYY HH24:MI:SS') ,TO_DATE('29-APR-2011 17:17:26','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO konferencje VALUES (3, 'konferencje 3', 'Warszawa' ,TO_DATE('11-JAN-2011 09:14:08','DD-MON-YYYY HH24:MI:SS') ,TO_DATE('11-JAN-2011 18:14:08','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO konferencje VALUES (4, 'konferencje 4', 'Warszawa' ,TO_DATE('09-FEB-2012 10:14:13','DD-MON-YYYY HH24:MI:SS') ,TO_DATE('09-FEB-2012 16:14:13','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO konferencje VALUES (5, 'konferencje 5', 'Warszawa' ,TO_DATE('23-MAY-2012 10:10:23','DD-MON-YYYY HH24:MI:SS') ,TO_DATE('23-MAY-2012 17:16:23','DD-MON-YYYY HH24:MI:SS') );

            INSERT INTO przedmioty VALUES (1, 'Sztuczna inteligencja' );
            INSERT INTO przedmioty VALUES (2, 'Bazy danych' );
            INSERT INTO przedmioty VALUES (3, 'Inzynieria oprogramowania' );
            INSERT INTO przedmioty VALUES (4, 'Matematyka' );
            INSERT INTO przedmioty VALUES (5, 'Algorytmy i struktury danych' );

            INSERT INTO FormyPrzedmiotow VALUES (1, 1, 'forma 1', 1, TO_DATE('16-MAY-2010 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (2, 1, 'forma 2', 2, TO_DATE('16-JAN-2012 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (3, 2, 'forma 3', 3, TO_DATE('16-APR-2012 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (4, 2, 'forma 4', 4, TO_DATE('16-APR-2011 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (5, 3, 'forma 5', 5, TO_DATE('16-JUN-2012 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (6, 4, 'forma 6', 6, TO_DATE('16-FEB-2011 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (7, 5, 'forma 7', 7, TO_DATE('16-MAY-2012 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (8, 3, 'forma 8', 1, TO_DATE('16-APR-2012 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (9, 1, 'forma 9', 3, TO_DATE('16-JAN-2012 09:00:00','DD-MON-YYYY HH24:MI:SS') );
            INSERT INTO FormyPrzedmiotow VALUES (10, 2, 'forma 10',2, TO_DATE('16-FEB-2012 09:00:00','DD-MON-YYYY HH24:MI:SS') );


            INSERT INTO uczelnie VALUES (1, 'Politechnika Warszawska' , 'ul. Matuszewskiego 82; ','06-300', 'Przasnysz', 7582313 , 9084546646);
            INSERT INTO uczelnie VALUES (2, 'Politechnika Wroclawska' , 'ul. Polna 7/6; ','09-221', 'Poznan', 7562213 , 9014546645);
            INSERT INTO uczelnie VALUES (3, 'Politechnika Gdanska' , 'ul. Figarskiego 6; ','08-675', 'Kielce', 7362316 , 2084244444);
            INSERT INTO uczelnie VALUES (4, 'Politechnika Slaska' , 'ul. Dluga 51; ','08-675', 'Poznan', 7362217 , 1222521111);
            INSERT INTO uczelnie VALUES (5, 'Politechnika Łódzka' , 'ul. Macieja 31; ','08-675', 'Poznan', 7568288 , 1014546778);

            INSERT INTO wydzialy VALUES (1,'Organizacji i Zarządzania', 2 );
            INSERT INTO wydzialy VALUES (2,'Elektrotechniki Informatyki', 1 );
            INSERT INTO wydzialy VALUES (3,'Mechaniczny' , 5);
            INSERT INTO wydzialy VALUES (4,'Inzynierii Procesowej', 1 );
            INSERT INTO wydzialy VALUES (5,'Fizyki Technicznej' ,3);

            INSERT INTO katedry VALUES (1, 'Zarządzania',2 );
            INSERT INTO katedry VALUES (2, 'Elektrotechniki',1 );
            INSERT INTO katedry VALUES (3, 'Mechaniczna' ,5);
            INSERT INTO katedry VALUES (4, 'Inzynierii',1 );
            INSERT INTO katedry VALUES (5, 'Fizyki' ,3);

            INSERT INTO pracownicy VALUES (1,'Crocitto', 'Fred' ,'doktor', 'ul. Polna 7/6; ','09-221', 'Poznan', 5352217 , TO_DATE('16-MAY-1990 09:00:00','DD-MON-YYYY HH24:MI:SS'), 1);
            INSERT INTO pracownicy VALUES (2,'Landry', 'Laetia.' ,'doktor', 'ul. Dluga 51; ','08-675', 'Poznan', 2252259 , TO_DATE('09-FEB-1970 05:20:08','DD-MON-YYYY HH24:MI:SS'), 2);
            INSERT INTO pracownicy VALUES (3,'Enison', 'Laetia' ,'doktor', 'ul. Macieja 31; ','08-675', 'Poznan', 8262215 , TO_DATE('19-JAN-1970 10:00:09','DD-MON-YYYY HH24:MI:SS'), 3);
            INSERT INTO pracownicy VALUES (4,'Moskowitz','Angel' ,'magister', 'ul. Figarskiego 6; ','08-675', 'Kielce', 7362218 , TO_DATE('17-FEB-1970 03:10:10','DD-MON-YYYY HH24:MI:SS'), 5);
            INSERT INTO pracownicy VALUES (5,'Olvsade', 'Judith' ,'profesor', 'ul. Figarskiego 6; ','08-675', 'Warszawa', 6362187 , TO_DATE('11-MAR-1962 12:08:11','DD-MON-YYYY HH24:MI:SS'), 4);
            INSERT INTO pracownicy VALUES (6,'Mierzwa', 'Catherine','profesor', 'ul. Dluga 01; ','08-685', 'Warszawa', 7462115 , TO_DATE('14-JAN-1970 13:00:01','DD-MON-YYYY HH24:MI:SS'), 2);
            INSERT INTO pracownicy VALUES (7,'Sethi', 'Judy' ,'doktor habilitowany', 'ul. Polna 6; ','09-281', 'Poznan',7462317, TO_DATE('23-MAR-1979 15:06:23','DD-MON-YYYY HH24:MI:SS'), 5);
            INSERT INTO pracownicy VALUES (8,'Walter', 'Larry' ,'doktor habilitowany', 'ul. Macieja 31; ','08-675', 'Poznan',7345218, TO_DATE('20-FEB-1959 01:04:07','DD-MON-YYYY HH24:MI:SS'), 1);



            INSERT INTO referaty VALUES (1, 'baza Acces',TO_DATE('13-MAR-2012 10:05:06','DD-MON-YYYY HH24:MI:SS'),2 );
            INSERT INTO referaty VALUES (2, 'Eksploracja danych',TO_DATE('09-MAY-2012 12:00:06','DD-MON-YYYY HH24:MI:SS'),1 );
            INSERT INTO referaty VALUES (3, 'Podstawy rysunku technicznego' ,TO_DATE('05-FEB-2012 14:10:06','DD-MON-YYYY HH24:MI:SS'),5);
            INSERT INTO referaty VALUES (4, 'Animacja i wideo',TO_DATE('27-FEB-2012 12:10:06','DD-MON-YYYY HH24:MI:SS'),1 );
            INSERT INTO referaty VALUES (5, 'Szkolenie BHP' ,TO_DATE('21-MAY-2012 16:10:06','DD-MON-YYYY HH24:MI:SS'),3);

            INSERT INTO referatyPracownikow VALUES (1, 2 );
            INSERT INTO referatyPracownikow VALUES (2, 1 );
            INSERT INTO referatyPracownikow VALUES (4 , 6 );
            INSERT INTO referatyPracownikow VALUES (4, 1 );
            INSERT INTO referatyPracownikow VALUES (5, 3 );


            INSERT INTO publikacjePracownikow VALUES (1, 1 );
            INSERT INTO publikacjePracownikow VALUES (2, 3 );
            INSERT INTO publikacjePracownikow VALUES (3, 5 );
            INSERT INTO publikacjePracownikow VALUES (4, 1 );
            INSERT INTO publikacjePracownikow VALUES (5, 4 );

            INSERT INTO przedmiotyProwadzone VALUES (1, 1 ,160 );
            INSERT INTO przedmiotyProwadzone VALUES (2, 3 ,120 );
            INSERT INTO przedmiotyProwadzone VALUES (3, 5 ,140 );
            INSERT INTO przedmiotyProwadzone VALUES (4, 1 ,120 );
            INSERT INTO przedmiotyProwadzone VALUES (5, 4 ,100 );
            • 3. Re: max, sum function
              Stew Ashton
              If you look at your query, wydzialy is not joined to any other table, it is just there.

              Looking at your table design, the only path between NazwaWydzialu and nazwisko goes through the table KATEDRY.
              SELECT w.NazwaWydzialu, p.nazwisko
              FROM WYDZIALY W,
              KATEDRY K,
              PRACOWNICY P
              WHERE W.IDWYDZIALU = K.IDWYDZIALU
              AND K.IDKATEDRY = P.ID_KATEDRY
              order by 1,2;
              
              NAZWAWYDZIALU                  NAZWISKO                     
              ------------------------------ ------------------------------
              Elektrotechniki Informatyki    Crocitto                       
              Elektrotechniki Informatyki    Walter                         
              Fizyki Technicznej             Enison                         
              Mechaniczny                    Moskowitz                      
              Mechaniczny                    Sethi                          
              Organizacji i Zarządzania      Landry                         
              Organizacji i Zarządzania      Mierzwa                        
              Organizacji i Zarządzania      Olvsade
              That doesn't get the results you say you want.

              Can you explain the logic that leads to the results you asked for?
              • 4. Re: max, sum function
                user260user
                solved the problem together with a colleague,  but it works


                with
                x as (
                select k.Nazwakatedry, p.nazwisko, count(rp.idreferat) n
                from
                referatypracownikow rp
                join pracownicy p on rp.idpracownika = p.idpracownika
                join katedry k on p.id_katedry = k.idkatedry
                group by k.Nazwakatedry, p.nazwisko
                ), y as (
                select x.*, rank() over ( partition by x.Nazwakatedry, x.nazwisko order by n desc ) rnk
                from x
                )
                select y.Nazwakatedry, y.nazwisko, y.n
                from y
                where rnk = 1
                group by y.Nazwakatedry, y.nazwisko;

                NAZWAKATEDRY | NAZWISKO | Y.N
                Elektrotechniki     |Landry     |1
                Elektrotechniki     |Mierzwa     |1
                Mechaniczna     |Enison     |1
                Zarządzania     |Crocitto     |2
                • 5. Top-N Query
                  Frank Kulash
                  Hi,
                  user13387916 wrote:
                  solved the problem together with a colleague,  but it works


                  with
                  x as (
                  select k.Nazwakatedry, p.nazwisko, count(rp.idreferat) n
                  from
                  referatypracownikow rp
                  join pracownicy p on rp.idpracownika = p.idpracownika
                  join katedry k on p.id_katedry = k.idkatedry
                  group by k.Nazwakatedry, p.nazwisko
                  ), y as (
                  select x.*, rank() over ( partition by x.Nazwakatedry, x.nazwisko order by n desc ) rnk
                  from x
                  )
                  select y.Nazwakatedry, y.nazwisko, y.n
                  from y
                  where rnk = 1
                  group by y.Nazwakatedry, y.nazwisko;
                  Dos that really work for you? When I try it, I get the error "ORA-00979: not a GROUP BY expression" because n in the main query is in the SELECT clause, but not in the GROUP BY clause.
                  You don't need the GROUP BY clause in the main query. You don't need 2 sub-queries, either; just 1 sub-query will do:
                  WITH     x     AS 
                  (
                       SELECT    k.nazwakatedry
                       ,        p.nazwisko
                       ,       COUNT (rp.idreferat)     AS n
                       ,       RANK () OVER ( PARTITION BY  k.nazwakatedry
                                             ,             p.nazwisko 
                                        ORDER BY            COUNT (rp.idreferat)     DESC 
                                      )           AS rnk
                       FROM       referatypracownikow     rp
                       JOIN       pracownicy          p   ON   rp.idpracownika = p.idpracownika
                       JOIN       katedry          k   ON      p.id_katedry      = k.idkatedry
                       GROUP BY  k.nazwakatedry
                       ,            p.nazwisko
                  )
                  SELECT  nazwakatedry
                  ,      nazwisko
                  ,      n
                  FROM     x
                  WHERE     rnk = 1
                  ;
                  Analytic functiions (such as RANK) are computed after the GROUP BY clause has been applied and aggregate functions (such as COUNT in the example above) have been computed.
                  • 6. Re: Top-N Query
                    user260user
                    I'm sorry that I wrote before

                    Thank you very much for your help