Page 1 sur 1

[Base] Présentation de données sous forme d'analyse croisée

MessagePublié: 09 Mars 2012 14:05
par Piaf
L'idée de départ et la procédure à suivre sont de Bernard Andruccioli dans Son tutoriel
sur OpenOffice.Base que l'on peut récupérer Ici
Les données choisies pour l'exemple sont limitées.
Elles ne servent qu'à illustrer la procédure à suivre pour obtenir le résultat escompté.
On dispose donc d'un certain nombre de vendeurs avec leur chiffre de vente quotidien.
    Macheprot Claude 03/01/12 50,00 €
    Macheprot Claude 04/01/12 75,00 €
    Macheprot Claude 26/01/12 35,00 €
    Macheprot Claude 08/02/12 60,00 €
    Macheprot Claude 22/02/12 20,00 €
    Macheprot Claude 25/02/12 100,00 €
    Macheprot Claude 05/03/12 25,00 €
    Macheprot Claude 06/03/12 30,00 €
    Macheprot Claude 07/03/12 18,00 €
    Duguidon Antoine 10/01/12 25,00 €
    Duguidon Antoine 18/01/12 60,00 €
    Duguidon Antoine 26/01/12 35,00 €
    Duguidon Antoine 14/02/12 80,00 €
    Duguidon Antoine 23/02/12 20,00 €
    Duguidon Antoine 28/02/12 50,00 €
    Duguidon Antoine 05/03/12 35,00 €
    Duguidon Antoine 06/03/12 45,00 €
    Duguidon Antoine 07/03/12 45,00 €
    Aymé Marcel 09/01/12 40,00 €
    Aymé Marcel 14/01/12 20,00 €
    Aymé Marcel 24/01/12 35,00 €
    Aymé Marcel 07/02/12 70,00 €
    Aymé Marcel 17/02/12 30,00 €
    Aymé Marcel 21/02/12 35,00 €
    Aymé Marcel 05/03/12 80,00 €
    Aymé Marcel 06/03/12 25,00 €
    Aymé Marcel 08/03/12 35,00 €
Dans un premier temps, on crée une requête par mois calculant la somme des ventes par vendeur.
Limité pour l'exemple aux trois premiers mois de l'année.
Pour la requête rTotalJanvier
RequêteTotalJanvier.png
Les noms prénoms sont concaténés
Code : Tout sélectionner   AgrandirRéduire
CONCAT( CONCAT( [tVendeurs].[Prenom], ' ' ), [tVendeurs].[Nom] )

le regroupement et le critère sur le mois
Code : Tout sélectionner   AgrandirRéduire
MONTHNAME( [tCa].[Date] )

La fonction MONTHNAME est choisie pour que ce soit plus parlant visuellement, la fonction MONTH ferait tout aussi bien l'affaire.
L'astuce consiste à donner comme Alias de "Ca" le nom du mois concernant la requête, on utilisera cet Alias comme titre de colonne.
Même manip pour Février et Mars.
Une fois terminé on dispose de trois requêtes que l'on va utiliser dans une quatrième pour l'analyse.
RequêteAnalyse.png
On met en relation les trois requêtes sur le nom des vendeurs.
Cette façon de faire ne fonctionne qu'à partir du moment où il existe au moins une entrée par mois pour chaque vendeur.
En espérant que ça puisse être utile.

Re: [Base] Analyse croisée SQL direct

MessagePublié: 07 Juil 2017 13:45
par Piaf
Bonjour
En complément au message précédent, il est possible d'obtenir le même résultat avec une requête en SQL direct.
L'instruction SQL
SQLdirect.png
Résultats dans un formulaire.
Form.png
A+

Re: [Base] Analyse croisée SQL direct 2

MessagePublié: 10 Avr 2019 13:09
par Piaf
Bonjour
Une autre façon (plus simple :) ) d'obtenir le même résultat.
Pour HSQL
Code : Tout sélectionner   AgrandirRéduire
SELECT CONCAT( CONCAT( "Nom", ' ' ), "Prenom" ) "Vendeur",
SUM( CASE WHEN MONTH( "tCa"."Date" ) = 1 THEN "Ca" ELSE 0 END ) "Janvier",
SUM( CASE WHEN MONTH( "tCa"."Date" ) = 2 THEN "Ca" ELSE 0 END ) "Février",
SUM( CASE WHEN MONTH( "tCa"."Date" ) = 3 THEN "Ca" ELSE 0 END ) "Mars",
SUM( CASE WHEN MONTH( "tCa"."Date" ) = 4 THEN "Ca" ELSE 0 END ) "Avril",
SUM( "Ca" ) "Total lignes"
FROM "tCa", "tVendeurs" WHERE "tCa"."IdVendeur" = "tVendeurs"."IdVendeur" GROUP BY CONCAT( CONCAT( "Nom", ' ' ), "Prenom" )
Pour FB
Code : Tout sélectionner   AgrandirRéduire
SELECT COALESCE ( "tVendeurs"."Prenom" || ' ' || "tVendeurs"."Nom", "Prenom", "Nom" ) "Vendeur",
SUM( CASE WHEN EXTRACT( MONTH FROM "Date" ) = 1 THEN "Ca" ELSE 0 END ) "Janvier",
SUM( CASE WHEN EXTRACT( MONTH FROM "Date" ) = 2 THEN "Ca" ELSE 0 END ) "Février",
SUM( CASE WHEN EXTRACT( MONTH FROM "Date" ) = 3 THEN "Ca" ELSE 0 END ) "Mars",
SUM( CASE WHEN EXTRACT( MONTH FROM "Date" ) = 4 THEN "Ca" ELSE 0 END ) "Avril", SUM( "tCa"."Ca" ) "Total lignes"
FROM "tCa", "tVendeurs" WHERE "tCa"."IdVendeur" = "tVendeurs"."IdVendeur" GROUP BY "Vendeur"

En créant une vue à partir de cette requête, il est possible d'obtenir la somme par colonne, une requête UNION
Code : Tout sélectionner   AgrandirRéduire
SELECT * FROM "Vue1"
UNION
SELECT 'Total Colonnes', SUM( "Janvier" ) "SJ", SUM( "Février" ) "SF", SUM( "Mars" ) "SM", SUM( "Avril" ) "SA", SUM( "Total lignes" ) "ST" FROM "Vue1"
permet donc d'obtenir
Analyse.png
A+