[quote="Hubert Roksor"]Je m'aperçois que les requêtes utilisent MP.quantite alors que ça devrait être RC.quantite. J'imagine que MP.quantite correspond au stock de chaque magasin.[/quote]
MP.quantité correspond à la quantité pour un magasin donné.
Ex : j'achète une boite de Maïzena(id = 2) dans le magasin 1 qui fait 200g et une autre dans le magasin 2 qui fait 400g, dans la table ca donne :
id magasin quantite prix
2 1 0.200 1€50
2 2 0.400 2€50
avec les requêtes, celà devrait donner :
prix moyen = ((1,5/0,2) + (2,5/0,4))/2. (ca me donne le prix moyen au kg, plus facile après pour comparer les prix en magasin).
dans la recette, j'utilise 50g de Maïzena, donc coût de l'ingrédiant dans la recette = prix moyen * 0,05.
Ensuite, je prend la somme de l'ensemble des ingrédiants pour connaître le cout moyen d'une recette.

Le but du jeux, c'est ensuite de pouvoir comparer entre une recette maison et une recette industrielle.

Là, on a la première requête qui est fausse.
Je vais regarder celà tranquillement ce soir pour corriger.
Mais à priori, je ne peux pas échapper aux sous-requêtes.

EDIT
Bon, je viens de regarder, ca donne :
[code]SELECT RC.id_recette, SUM( RC.quantite * PP.prix ) AS cout_recette
FROM (

SELECT MP.id_produit, AVG( MP.prix / MP.quantite_article ) AS prix
FROM recettes_magasins_prix MP
GROUP BY MP.id_produit
ORDER BY NULL
) AS PP
JOIN recettes_composition RC
USING ( id_produit )
GROUP BY RC.id_recette
LIMIT 0 , 30 [/code]
A vu de nez, ca semble cohérent.

Merci.

Je m'aperçois que les requêtes utilisent MP.quantite alors que ça devrait être RC.quantite. J'imagine que MP.quantite correspond au stock de chaque magasin.

En reprenant tout depuis le début :

Prix moyen de chaque produit
[code] SELECT id_produit, AVG(prix) AS prix
FROM recettes_magasins_prix
GROUP BY id_produit
ORDER BY NULL[/code]

Coût moyen de chaque produit pour chaque recette
[code]SELECT RC.id_recette, RC.id_produit, RC.quantite * PP.prix AS cout_produit
FROM (
SELECT id_produit, AVG(prix) AS prix
FROM recettes_magasins_prix
GROUP BY id_produit
ORDER BY NULL
) AS PP
JOIN recettes_composition RC USING (id_produit)[/code]

...la même chose en faisant la somme des coûts de tous les produits pour chaque recette
[code] SELECT RC.id_recette, SUM(RC.quantite * PP.prix) AS cout_recette
FROM (
SELECT id_produit, AVG(prix) AS prix
FROM recettes_magasins_prix
GROUP BY id_produit
ORDER BY NULL
) AS PP
JOIN recettes_composition RC USING (id_produit)
GROUP BY RC.id_recette[/code]

Normalement, à partir de là tu devrais pouvoir joindre la table des recettes pour avoir leur nom. La requête n'est pas optimale si les recettes n'utilisent pas la majorité des produits disponibles dans les magasins, mais sinon elle devrait être correcte.

J'avais un doute d'ou ma réponse au dessus.
Je viens de vérifier.
Si j'ai un même produit dans 3 magasins différents, ca me fait le cumul.

En faisant la 2eme requête, que j'avais déjà testé :
[quote]SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM( RC.quantite * AVG( MP.prix / MP.quantite_article ) ) AS prix_moyen
FROM recettes_magasins_prix MP
JOIN recettes_composition RC
USING ( id_produit )
JOIN recettes_recettes RR
USING ( id_recette )
GROUP BY RC.id_recette
ORDER BY RR.label_recette[/quote]
j'ai comme erreur : Invalid use of group function


PS : j'ai profité du passage en PDO pour changer les noms de mes tables.

En effet, en y réfléchissant, la façon de calculer le prix moyen ne sera pas la bonne. Est-ce que tu cherches le coût moyen d'une recette en achetant les produits dans différents magasins ou le coût moyen de chaque recette en achetant tout dans le même magasin ? D'ailleurs... je me demande si ça ne revient pas au même :-k

Note que je doute que l'algorithme plus haut donne l'un ou l'autre, puisqu'il multiple le total des quantités par le prix moyen de tous les ingrédients dans tous les magasins. En fait il faudrait calculer la somme de la multiplication du prix moyen par la quantité, ce qui donnerait
[code] SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(RC.quantite * AVG(MP.prix / MP.quantite_article)) AS prix_moyen
FROM magasin_produit MP
JOIN recette_composition RC USING (id_produit)
JOIN recette RR USING (id_recette)
GROUP BY RC.id_recette
ORDER BY RR.label_recette[/code]
Ou, en cas d'erreur ci-dessus
[code] SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(PP.quantite * PP.prix_moyen_produit) AS prix_moyen_recette
FROM (
SELECT RC.id_recette, RC.quantite, AVG(MP.prix / MP.quantite_article) AS prix_moyen_produit
FROM magasin_produit MP
JOIN recette_composition RC USING (id_produit)
GROUP BY RC.id_recette, RC.id_produit
) AS PP
JOIN recette RR USING (id_recette)
GROUP BY RR.id_recette
ORDER BY RR.label_recette[/code]
D'ailleurs, dans cette requête on devrait pouvoir calculer le coût de chaque produit (prix moyen * quantite) dans la table dérivée
[code] SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(PP.cout_produit) AS prix_moyen_recette
FROM (
SELECT RC.id_recette, RC.quantite * AVG(MP.prix / MP.quantite_article) AS cout_produit
FROM magasin_produit MP
JOIN recette_composition RC USING (id_produit)
GROUP BY RC.id_recette, RC.id_produit
) AS PP
JOIN recette RR USING (id_recette)
GROUP BY RR.id_recette
ORDER BY RR.label_recette[/code]
...et c'est à ce moment là que je m'aperçois que cette requête fait la même chose que la première du post, si ce n'est qu'elle fonctionne sous PostgreSQL et qu'elle pourrait être plus rapide sur de grosses recettes (à vérifier).

Merci.

L'affichage est correct, j'ai bien une ligne par recette.
Je n'ai plus qu'à vérifier que les valeurs sont bonnes.

Si tu ne GROUP pas BY id_produit tu devrais logiquement avoir le total par recette, n'est-ce pas le cas ?

En élaguant la requête pour profiter des raccourcis de SQL, ça nous donne[code] SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(RC.quantite) * AVG(MP.prix / MP.quantite_article) AS prix_moyen
FROM magasin_produit MP
JOIN recette_composition RC USING (id_produit)
JOIN recette RR USING (id_recette)
GROUP BY RC.id_recette
ORDER BY RR.label_recette[/code]
Les espaces partout [url=http://www.dailymotion.com/video/x1gbaf_hassan-cehef_fun]c'est cadeaux, ça fait plaisir[/url].

J'utilise mySQL 5.0.27.

J'ai une base avec des recettes et je voudrais calculer le cout de revient des recettes.
Pour celà, je prends le prix moyen des ingrédiants utilisés x la quantité et je fais la somme des divers ingrédiants.

Dans la base, j'ai :
- une table avec des prix issus de différents magasins ;
- une table avec les ingrédients qui composent de la recette ;
- une table avec la recette.

Lorsque je fais cette requête, je me retrouve avec une ligne par ingrédiant, donc 10 ingrédiants me donnent 10 lignes.
[code]SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(RC.quantite) * AVG(MP.prix / MP.quantite_article)
FROM magasin_produit MP
INNER JOIN recette_composition RC
ON MP.id_produit = RC.id_produit
INNER JOIN recette RR
ON RC.id_recette = RR.id_recette
GROUP BY MP.id_produit, RC.id_recette
ORDER BY RR.label_recette
[/code]

La structure des 3 tables concernées :
[code]-- Structure de la table `magasin_produit`
--

CREATE TABLE `magasin_produit` (
`id_produit` int(11) NOT NULL,
`id_magasin` int(11) NOT NULL,
`prix` float(5,2) NOT NULL,
`quantite_article` float(6,2) NOT NULL,
`date_maj` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id_produit`,`id_magasin`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `recette`
--

CREATE TABLE `recette` (
`id_recette` int(10) unsigned NOT NULL auto_increment,
`type_recette` tinyint(3) unsigned NOT NULL default '0',
`nb_part` tinyint(4) NOT NULL,
`label_recette` varchar(40) NOT NULL,
`origine_recette` varchar(200) NOT NULL,
`resume_recette` text NOT NULL,
`url_origine_recette` varchar(200) NOT NULL,
`description_recette` text,
`visible_prive` char(1) NOT NULL default 'P',
PRIMARY KEY (`id_recette`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

--
-- Structure de la table `recette_composition`
--

CREATE TABLE `recette_composition` (
`id_produit` tinyint(4) NOT NULL default '0',
`id_recette` int(11) NOT NULL,
`quantite` float(6,3) NOT NULL default '0.000',
KEY `id_part` (`id_produit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;[/code]

Merci du coup de main.