i have query i've been working on, solution found having average result per pecheur (fisherman), yes bit cheasy it's school project. need name , adress of every fisherman have minimum of 2 charges (frais) on every of contract (contrat), , average of 2 fish catches (prises) per year between 2000 , 2002 inclusively. necessary tables listed in joins, question is, can use having avg(x) although x not listed in select nor in group by, appears in table in section. please note don't have real db test query.
select pecheur.nom nom_pecheur, pecheur.adresse adresse tp1_pecheur pecheur inner join tp1_contrat contrat on pecheur.id_pecheur=contrat.id_pecheur inner join tp1_frais_contrat frais_contrat on contrat.id_contrat=frais_contrat.id_contrat inner join tp1_frais frais on frais_contrat.id_frais=frais.id_frais inner join tp1_declaration_prise declaration_prise on pecheur.id_pecheur=declaration_prise.id_pecheur inner join tp1_prise prise on declaration_prise.id_declaration_prise=prise.id_declaration_prise frais_contrat.id in ( select tp1_frais_contrat.id, count(*) nb_frais tp1_frais_contrat group tp1_frais_contrat.id having nb_frais >= 2 ) , declaration_prise.date_declaration between '01-01-2000' , '31-12-2002' group nom_pecheur having avg(prise.nombre_poisson) > 2;
...my question is, can use having avg(x) although x not listed in select nor in group by...
the short answer 'yes'.
select colb, colc testing group colb, colc having avg(cola) > 4; 