7 | | select q4.god as godina, p.ime as predmet, q4.broj_casovi as broj_casovi |
8 | | from |
9 | | ( |
10 | | |
11 | | select * from |
12 | | ( |
13 | | select q1.godina as god, max(q1.broj_casovi) as maksimum from |
14 | | ( |
15 | | select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi |
16 | | from predmeti p |
17 | | join casovi c on c.id_predmet = p.id_predmet |
18 | | group by godina,predmet |
19 | | ) q1 |
20 | | group by godina |
21 | | ) q2 |
22 | | join |
23 | | ( |
24 | | select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi |
25 | | from predmeti p |
26 | | join casovi c on c.id_predmet = p.id_predmet |
27 | | group by godina,predmet |
28 | | ) q3 |
29 | | on q2.god=q3.godina and q2.maksimum=q3.broj_casovi |
30 | | |
31 | | ) q4 |
32 | | join predmeti p on p.id_predmet = q4.predmet |
33 | | order by godina desc; |
| 7 | select q4."location", q4.max_broj_nastani |
| 8 | from ( |
| 9 | (select extract (year from t.date) as godina, count(distinct e.id_event) broj_nastani, t."location" |
| 10 | from ticket t |
| 11 | join events e on t.id_event =e.id_event |
| 12 | group by godina, t."location" |
| 13 | |
| 14 | ) q1 |
| 15 | |
| 16 | join |
| 17 | ( |
| 18 | select max(q2.broj_nastani) as max_broj_nastani |
| 19 | from ( |
| 20 | select extract (year from t.date) as godina, count(distinct e.id_event) broj_nastani, t."location" |
| 21 | from ticket t |
| 22 | join events e on t.id_event =e.id_event |
| 23 | group by godina, t."location" |
| 24 | ) q2 |
| 25 | ) q3 |
| 26 | on q1.broj_nastani=q3.max_broj_nastani) q4 |