una cosa del tipo AVG(COUNT(*))

[luca7]

ho un database semplice semplice (vedi allegato) e devo recuperare questo:

Recupero del nome, cognome e nazionalità dei piloti che nel 2004 hanno vinto un numero di gran premi maggiore del numero di gran premi vinti in media in un anno

io riesco a farmi dire per ogni anno quanti gran premi hanno vinto i piloti (che hanno vinto almeno un gran premio) però poi non riesco a farci la media su questi valori. La query sarebbe:

SELECT anno, nome, cognome, nazionalità, COUNT(*)
FROM granpremi
JOIN piloti ON granpremi.codpilvincitore = piloti.codpil
GROUP BY anno, codpil

ma poi non riesco ad andare avanti e fare la media per ogni pilota dei gran premi vinti in media in un anno

come posso fare (evitando le viste che mi fanno crashare il server?!?!?!?)

[skidx]

Originariamente inviato da luca7
Recupero del nome, cognome e nazionalità dei piloti che nel 2004 hanno vinto un numero di gran premi maggiore del numero di gran premi vinti in media in un anno

in media in un anno da chi?
media calcolata su quali dati?
Non sei stato molto chiaro

[kuarl]

così ad occhio dovresti fare una select annidata (solo con mysql >= 4.1), quella che hai postato è la principale, poi puoi fare una condizione aggregata (having) che coinvolge una sottoquery che calcola la media annuale dei gran premi vinti

se non hai mysql 4.1 l'unica è fare due query distinte

[luca7]

Originariamente inviato da skidx
in media in un anno da chi?
media calcolata su quali dati?
Non sei stato molto chiaro

Il testo è quello (è un esercizio universitario), in effetti anche io all'inizio non avevo capito molto bene

Comunque è una cosa del genere. Mettiamo che Schumacher abbia cominciato nel 2003; ha vinto 3 gran premi, poi nel 2004 ne ha vinti 10 e nel 2005 ne ha vinti 15. La media all'anno di Schumacher è di 5 gran premi vinti all'anno e dato che nel 2004 ha vinto più gran premi della media dovrebbe uscire nel risultato finale. Stessa cosa per ogni pilota che però deve aver vinto nel 2004 più gran premi di quanti ne ha vinti in media all'anno

Originariamente inviato da kuarl
...poi puoi fare una condizione aggregata (having) che coinvolge una sottoquery che calcola la media annuale dei gran premi vinti

già ma come faccio la sottoquery che calcola la media? perchè la media andrebbe calcolata sui conteggi (COUNT(*)) o no?

[platone]

con l'ingegno puoi fare tutto in una query.... più tranquillametne con 2.

In ogni caso non sei stato chiaro!
Non ho capito se la media è di tutti i piloti del database, di un pilota particolare, di un pilota su un anno particolare, di tutti i piloti su un anno particolare, di tutti i piloti su tutti gli anni.

Insomma... il concetto di media è relativo... e su questo non ho ben capito.

[ipnotic]

non riesco a trovare l'allegato ke dicevi con la struttura del database....

[luca7]

cavolo mi sembrava di averlo inserito e inceve vedo ora che se inserisco il file, poi premo su anteprima l'allegato viene cancellato o cmq non caricato. E oltretutto che non posso allegare .txt
Lo incollo qui allora scustate per il post chilometrico

CREATE TABLE `granpremi` (
  `Anno` int(10) unsigned NOT NULL auto_increment,
  `Data` date NOT NULL,
  `NomeCircuito` varchar(30) NOT NULL,
  `CodPilVincitore` tinyint(3) unsigned NOT NULL,
  `CodPilPolePosition` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`Anno`,`NomeCircuito`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2006 ;


INSERT INTO `granpremi` VALUES (1990, '2006-02-02', 'Hockenheim', 2, 6);
INSERT INTO `granpremi` VALUES (1990, '2006-02-02', 'Imola', 2, 2);
INSERT INTO `granpremi` VALUES (1990, '2006-02-02', 'Indianapolis', 2, 2);
INSERT INTO `granpremi` VALUES (1990, '2006-02-02', 'Interlagos', 7, 5);
INSERT INTO `granpremi` VALUES (1990, '2006-02-02', 'Melbourne', 7, 2);
INSERT INTO `granpremi` VALUES (1990, '2006-02-02', 'Monza', 4, 2);
INSERT INTO `granpremi` VALUES (1990, '2006-02-02', 'Silverstone', 5, 5);
INSERT INTO `granpremi` VALUES (1991, '2006-02-02', 'Hockenheim', 2, 7);
INSERT INTO `granpremi` VALUES (1991, '2006-02-02', 'Imola', 4, 3);
INSERT INTO `granpremi` VALUES (1991, '2006-02-02', 'Indianapolis', 3, 3);
INSERT INTO `granpremi` VALUES (1991, '2006-02-02', 'Interlagos', 7, 2);
INSERT INTO `granpremi` VALUES (1991, '2006-02-02', 'Melbourne', 5, 2);
INSERT INTO `granpremi` VALUES (1991, '2006-02-02', 'Monza', 4, 2);
INSERT INTO `granpremi` VALUES (1991, '2006-02-02', 'Silverstone', 3, 6);
INSERT INTO `granpremi` VALUES (1992, '2006-02-02', 'Hockenheim', 5, 1);
INSERT INTO `granpremi` VALUES (1992, '2006-02-02', 'Imola', 1, 3);
INSERT INTO `granpremi` VALUES (1992, '2006-02-02', 'Indianapolis', 5, 1);
INSERT INTO `granpremi` VALUES (1992, '2006-02-02', 'Interlagos', 6, 5);
INSERT INTO `granpremi` VALUES (1992, '2006-02-02', 'Melbourne', 2, 6);
INSERT INTO `granpremi` VALUES (1992, '2006-02-02', 'Monza', 5, 1);
INSERT INTO `granpremi` VALUES (1992, '2006-02-02', 'Silverstone', 4, 7);
INSERT INTO `granpremi` VALUES (1993, '2006-02-02', 'Hockenheim', 1, 2);
INSERT INTO `granpremi` VALUES (1993, '2006-02-02', 'Imola', 2, 1);
INSERT INTO `granpremi` VALUES (1993, '2006-02-02', 'Indianapolis', 1, 4);
INSERT INTO `granpremi` VALUES (1993, '2006-02-02', 'Interlagos', 5, 5);
INSERT INTO `granpremi` VALUES (1993, '2006-02-02', 'Melbourne', 1, 7);
INSERT INTO `granpremi` VALUES (1993, '2006-02-02', 'Monza', 2, 1);
INSERT INTO `granpremi` VALUES (1993, '2006-02-02', 'Silverstone', 1, 2);
INSERT INTO `granpremi` VALUES (1994, '2006-02-02', 'Hockenheim', 1, 1);
INSERT INTO `granpremi` VALUES (1994, '2006-02-02', 'Imola', 5, 1);
INSERT INTO `granpremi` VALUES (1994, '2006-02-02', 'Indianapolis', 5, 2);
INSERT INTO `granpremi` VALUES (1994, '2006-02-02', 'Interlagos', 7, 4);
INSERT INTO `granpremi` VALUES (1994, '2006-02-02', 'Melbourne', 4, 3);
INSERT INTO `granpremi` VALUES (1994, '2006-02-02', 'Monza', 3, 3);
INSERT INTO `granpremi` VALUES (1994, '2006-02-02', 'Silverstone', 6, 4);
INSERT INTO `granpremi` VALUES (1995, '2006-02-02', 'Hockenheim', 3, 1);
INSERT INTO `granpremi` VALUES (1995, '2006-02-02', 'Imola', 1, 7);
INSERT INTO `granpremi` VALUES (1995, '2006-02-02', 'Indianapolis', 1, 3);
INSERT INTO `granpremi` VALUES (1995, '2006-02-02', 'Interlagos', 5, 5);
INSERT INTO `granpremi` VALUES (1995, '2006-02-02', 'Melbourne', 1, 3);
INSERT INTO `granpremi` VALUES (1995, '2006-02-02', 'Monza', 5, 5);
INSERT INTO `granpremi` VALUES (1995, '2006-02-02', 'Silverstone', 5, 1);
INSERT INTO `granpremi` VALUES (1996, '2006-02-02', 'Hockenheim', 4, 5);
INSERT INTO `granpremi` VALUES (1996, '2006-02-02', 'Imola', 2, 3);
INSERT INTO `granpremi` VALUES (1996, '2006-02-02', 'Indianapolis', 2, 1);
INSERT INTO `granpremi` VALUES (1996, '2006-02-02', 'Interlagos', 5, 3);
INSERT INTO `granpremi` VALUES (1996, '2006-02-02', 'Melbourne', 2, 1);
INSERT INTO `granpremi` VALUES (1996, '2006-02-02', 'Monza', 6, 6);
INSERT INTO `granpremi` VALUES (1996, '2006-02-02', 'Silverstone', 6, 4);
INSERT INTO `granpremi` VALUES (1997, '2006-02-02', 'Hockenheim', 4, 2);
INSERT INTO `granpremi` VALUES (1997, '2006-02-02', 'Imola', 2, 5);
INSERT INTO `granpremi` VALUES (1997, '2006-02-02', 'Indianapolis', 3, 4);
INSERT INTO `granpremi` VALUES (1997, '2006-02-02', 'Interlagos', 7, 2);
INSERT INTO `granpremi` VALUES (1997, '2006-02-02', 'Melbourne', 1, 5);
INSERT INTO `granpremi` VALUES (1997, '2006-02-02', 'Monza', 2, 5);
INSERT INTO `granpremi` VALUES (1997, '2006-02-02', 'Silverstone', 2, 3);
INSERT INTO `granpremi` VALUES (1998, '2006-02-02', 'Hockenheim', 3, 1);
INSERT INTO `granpremi` VALUES (1998, '2006-02-02', 'Imola', 4, 6);
INSERT INTO `granpremi` VALUES (1998, '2006-02-02', 'Indianapolis', 2, 1);
INSERT INTO `granpremi` VALUES (1998, '2006-02-02', 'Interlagos', 7, 7);
INSERT INTO `granpremi` VALUES (1998, '2006-02-02', 'Melbourne', 6, 3);
INSERT INTO `granpremi` VALUES (1998, '2006-02-02', 'Monza', 3, 5);
INSERT INTO `granpremi` VALUES (1998, '2006-02-02', 'Silverstone', 4, 6);
INSERT INTO `granpremi` VALUES (1999, '2006-02-02', 'Hockenheim', 6, 5);
INSERT INTO `granpremi` VALUES (1999, '2006-02-02', 'Imola', 6, 6);
INSERT INTO `granpremi` VALUES (1999, '2006-02-02', 'Indianapolis', 4, 3);
INSERT INTO `granpremi` VALUES (1999, '2006-02-02', 'Interlagos', 3, 6);
INSERT INTO `granpremi` VALUES (1999, '2006-02-02', 'Melbourne', 1, 6);
INSERT INTO `granpremi` VALUES (1999, '2006-02-02', 'Monza', 3, 6);
INSERT INTO `granpremi` VALUES (1999, '2006-02-02', 'Silverstone', 5, 5);
INSERT INTO `granpremi` VALUES (2000, '2006-02-02', 'Hockenheim', 4, 7);
INSERT INTO `granpremi` VALUES (2000, '2006-02-02', 'Imola', 5, 4);
INSERT INTO `granpremi` VALUES (2000, '2006-02-02', 'Indianapolis', 6, 2);
INSERT INTO `granpremi` VALUES (2000, '2006-02-02', 'Interlagos', 1, 6);
INSERT INTO `granpremi` VALUES (2000, '2006-02-02', 'Melbourne', 6, 4);
INSERT INTO `granpremi` VALUES (2000, '2006-02-02', 'Monza', 1, 4);
INSERT INTO `granpremi` VALUES (2000, '2006-02-02', 'Silverstone', 4, 5);
INSERT INTO `granpremi` VALUES (2001, '2006-02-02', 'Hockenheim', 6, 6);
INSERT INTO `granpremi` VALUES (2001, '2006-02-02', 'Imola', 7, 6);
INSERT INTO `granpremi` VALUES (2001, '2006-02-02', 'Indianapolis', 2, 5);
INSERT INTO `granpremi` VALUES (2001, '2006-02-02', 'Interlagos', 7, 3);
INSERT INTO `granpremi` VALUES (2001, '2006-02-02', 'Melbourne', 2, 6);
INSERT INTO `granpremi` VALUES (2001, '2006-02-02', 'Monza', 5, 7);
INSERT INTO `granpremi` VALUES (2001, '2006-02-02', 'Silverstone', 7, 7);
INSERT INTO `granpremi` VALUES (2002, '2006-02-02', 'Hockenheim', 2, 6);
INSERT INTO `granpremi` VALUES (2002, '2006-02-02', 'Imola', 2, 7);
INSERT INTO `granpremi` VALUES (2002, '2006-02-02', 'Indianapolis', 4, 7);
INSERT INTO `granpremi` VALUES (2002, '2006-02-02', 'Interlagos', 1, 4);
INSERT INTO `granpremi` VALUES (2002, '2006-02-02', 'Melbourne', 3, 4);
INSERT INTO `granpremi` VALUES (2002, '2006-02-02', 'Monza', 2, 7);
INSERT INTO `granpremi` VALUES (2002, '2006-02-02', 'Silverstone', 3, 7);
INSERT INTO `granpremi` VALUES (2003, '2006-02-02', 'Hockenheim', 7, 6);
INSERT INTO `granpremi` VALUES (2003, '2006-02-02', 'Imola', 7, 2);
INSERT INTO `granpremi` VALUES (2003, '2006-02-02', 'Indianapolis', 1, 6);
INSERT INTO `granpremi` VALUES (2003, '2006-02-02', 'Interlagos', 3, 5);
INSERT INTO `granpremi` VALUES (2003, '2006-02-02', 'Melbourne', 5, 5);
INSERT INTO `granpremi` VALUES (2003, '2006-02-02', 'Monza', 1, 7);
INSERT INTO `granpremi` VALUES (2003, '2006-02-02', 'Silverstone', 5, 6);
INSERT INTO `granpremi` VALUES (2004, '2006-02-02', 'Hockenheim', 4, 3);
INSERT INTO `granpremi` VALUES (2004, '2006-02-02', 'Imola', 3, 1);
INSERT INTO `granpremi` VALUES (2004, '2006-02-02', 'Indianapolis', 4, 3);
INSERT INTO `granpremi` VALUES (2004, '2006-02-02', 'Interlagos', 3, 3);
INSERT INTO `granpremi` VALUES (2004, '2006-02-02', 'Melbourne', 1, 1);
INSERT INTO `granpremi` VALUES (2004, '2006-02-02', 'Monza', 6, 4);
INSERT INTO `granpremi` VALUES (2004, '2006-02-02', 'Silverstone', 5, 6);
INSERT INTO `granpremi` VALUES (2005, '2006-02-02', 'Hockenheim', 4, 2);
INSERT INTO `granpremi` VALUES (2005, '2006-02-02', 'Imola', 5, 5);
INSERT INTO `granpremi` VALUES (2005, '2006-02-02', 'Indianapolis', 6, 3);
INSERT INTO `granpremi` VALUES (2005, '2006-02-02', 'Interlagos', 6, 4);
INSERT INTO `granpremi` VALUES (2005, '2006-02-02', 'Melbourne', 5, 4);
INSERT INTO `granpremi` VALUES (2005, '2006-02-02', 'Monza', 4, 3);
INSERT INTO `granpremi` VALUES (2005, '2006-02-02', 'Silverstone', 2, 2);


CREATE TABLE `piloti` (
  `CodPil` tinyint(3) unsigned NOT NULL auto_increment,
  `Nome` varchar(30) NOT NULL,
  `Cognome` varchar(30) NOT NULL,
  `Nazionalità` varchar(30) NOT NULL,
  PRIMARY KEY  (`CodPil`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;


INSERT INTO `piloti` VALUES (1, 'Michael', 'Schumacher', 'Tedesco');
INSERT INTO `piloti` VALUES (2, 'Juan Pablo', 'Montoya', 'Argentino');
INSERT INTO `piloti` VALUES (3, 'David', 'Coulthard', 'Inglese');
INSERT INTO `piloti` VALUES (4, 'Kimi', 'Raikkonen', 'Italiano');
INSERT INTO `piloti` VALUES (5, 'Rubens', 'Barrichello', 'Francese');
INSERT INTO `piloti` VALUES (6, 'Giancarlo', 'Fisichella', 'Belga');
INSERT INTO `piloti` VALUES (7, 'Janson', 'Button', 'Spagnolo');

[ipnotic]

PENSO ke il consiglio ke ti hanno dato sopra di usare l'having è una cosa del genere....

SELECT anno, nome, cognome, nazionalità, COUNT(*)
FROM granpremi
JOIN piloti ON granpremi.codpilvincitore = piloti.codpil
GROUP BY anno, codpil
HAVING COUNT(*) > ( FAI LA SELECT PER TROVARE LA MEDIA DI GRANPREMI VINTI)

ora provo come fare quella select e poi ti faccio sapere......

[luca7]

Originariamente inviato da ipnotic
HAVING COUNT(*) > ( FAI LA SELECT PER TROVARE LA MEDIA DI GRANPREMI VINTI)

È proprio questo il problema, non so come fare la media perchè la media va fatta per ogni pilota e per ogni anno quindi media del count che ottengo dalla query proposta. Solo che AVG(COUNT(*)) non si può fare

[ipnotic]

ho provato a cercare qualkosa negli appunti dell'università e secondo me una cosa così secondo me si fa con le subquery nidificate.... tipo ho trovato un esercizio "quasi simile"...

Determinare gli impiegati che guadagnano più dello
stipendio medio del proprio reparto con ordinamento del
risultato per reparto.

SELECT Cognome, Nome, Stipendio, Reparto
FROM Impiegato X
WHERE Stipendio > ( SELECT AVG(Stipendio)
FROM Impiegato
WHERE X.Reparto=Reparto)
ORDER BY Reparto

----------

Questo tipo di query è chiamato correlato, perché ogni
esecuzione della subquery è correlata al valore di uno o
più attributi delle tuple candidate nella query esterna.
• Per poter riferire le colonne delle tuple candidate nella
query esterna si fa uso degli alias di relazione.
• L’alias di relazione è definito nella query esterna e riferito
nella query interna.

prova a vedere se ti puo' essere utile... come ispirazione...