1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| SELECT
B.code_famille,
B.Texte_famille,
A.NbParTrimestre,
SUM(B.montant),
A.montant / (SUM(B.montant) /100) as '%/Total',
A.Trimestre,
A.Annee,
A.montant
FROM
(SELECT /* Groupement par trimestres et années */
Code_famille,
SUM (montant) as montant
CASE
WHEN extract(MONTH FROM date) in (1,2,3) THEN 1
WHEN extract(MONTH FROM date) in (4,5,6) THEN 2
WHEN extract(MONTH FROM date) in (7,8,9) THEN 3
ELSE 4 END as Trimestre ,
extract(YEAR FROM date) as Annee,
COUNT(1) as NbParTrimestre
FROM
test
GROUP BY
Code_famille,
CASE
WHEN extract(MONTH FROM date) in (1,2,3) THEN 1
WHEN extract(MONTH FROM date) in (4,5,6) THEN 2
WHEN extract(MONTH FROM date) in (7,8,9) THEN 3
ELSE 4 END as Trimestre ,
extract(YEAR FROM date) as Annee
) A INNER JOIN test B
ON (A.code_famille = B.code_famille)
GROUP BY
B.code_famille,
B.Texte_famille,
A.NbParTrimestre,
A.Trimestre,
A.Annee,
A.montant |
Partager