soeretypo: extraction of product statistics

Goulwen: I've started to work on this new query. I'm not sure to full understand the request, so let me try to reformulate it:

The goal is to be able to view which how many products have been tested for the mineralization, the (bio)chemical composition and the IR spectrum, grouped by products types.

For testing that there's data for the mineralization, we look for results in the `mineralization` table.
For testing that there's data for the (bio)chemical composition, we look for results in the `composition` table.
For testing that there's data for the IR spectrum, we look for results in the `spectre` table.

For the mineralization, this leads for some difficulties:

* There's most of time 2 incubations by product, 1 for Carbon, 1 for Nitrogen, so the number of result is multiplied by 2 (but we can't be sure that there's always 2 incubations by product, if I remember correctly)
* The mineralization is followed on several days, which also leads to multiplied the number of products.

Is it possible to limit joins to the `echantillon` table and to consider that if there's a record in this table, we will have at least one result on the `mineralization` table?

The first draft of the query is available below:

SELECT
 DISTINCT
  tpdt_nom,
  COUNT(pdt_cn) AS nb_pdt,
  (
   SELECT COUNT(*)
   FROM type_produit type_produit_
   INNER JOIN produit produit_
    ON produit_.pdt_tpdt_cn = type_produit_.tpdt_cn
    INNER JOIN prelevement
     ON prv_pdt_cn = produit_.pdt_cn
      INNER JOIN echantillon
       ON ech_prv_cn = prv_cn
       INNER JOIN incubation
        ON inc_ech_cn = ech_cn
   WHERE
    type_produit_.tpdt_nom = type_produit.tpdt_nom

   GROUP BY
    type_produit_.tpdt_nom
   ORDER BY
    type_produit_.tpdt_nom

  ) AS nb_pdt_min,
  (
   SELECT COUNT(*)
   FROM type_produit type_produit_
   INNER JOIN produit produit_
    ON produit_.pdt_tpdt_cn = type_produit_.tpdt_cn
    INNER JOIN composition
     ON cmp_pdt_cn = produit_.pdt_cn
     INNER JOIN matiere_premiere
      ON mpr_cn = cmp_mpr_cn
   WHERE
    type_produit_.tpdt_nom = type_produit.tpdt_nom

   GROUP BY
    type_produit_.tpdt_nom
   ORDER BY
    type_produit_.tpdt_nom

  ) AS nb_pdt_compbio,
  (
   SELECT COUNT(*)
   FROM type_produit type_produit_
   INNER JOIN produit produit_
    ON produit_.pdt_tpdt_cn = type_produit_.tpdt_cn
    INNER JOIN prelevement
     ON prv_pdt_cn = produit_.pdt_cn
     INNER JOIN echantillon
      ON ech_prv_cn = prv_cn
      INNER JOIN spectre
       ON spe_ech_cn = ech_cn
   WHERE
    type_produit_.tpdt_nom = type_produit.tpdt_nom

   GROUP BY
    type_produit_.tpdt_nom
   ORDER BY
    type_produit_.tpdt_nom

  ) AS nb_pdt_spectre
FROM type_produit
INNER JOIN produit
 ON pdt_tpdt_cn = tpdt_cn
GROUP BY
 tpdt_nom
ORDER BY
 tpdt_nom