Champ temporaire à partir d'un autre champ

Bonjour
j'ai une table OPERATIONS avec un champ DATE (de type text) qui contient des données comme '2017-08-24'
je voudrais créer un champ temporaire NEWDAT dans une requête SQL qui par exemple ferait l'extraction de l'année et le mois '2017-08' par exemple
ainsi je pourrai faire une requête style pour avoir les agrégats par année-mois.

Code:

---

1 2 3

SELECT NEWDAT,SUM(MONTANT) FROM OPERATIONS GROUP BY NEWDAT

---

Est-ce possible ?

Merci d'avance

Cordialement

Bonjour,

Comme ceci?

Code:

---

1 2	SELECT substr(date, 1, 7) as NEWDAT,SUM(MONTANT) FROM OPERATIONS GROUP BY NEWDAT

---

Cordialement

Bonjour @acaumes et merci pour le code : :)
voila ce que ça donne :

Code:

---

1 2 3 4 5

SELECT substr(OPERATIONS.DATE, 1, 7) AS NEWDAT, OPERATIONS.CAT,OPERATIONS.RUB,SUM(OPERATIONS.MONTANT) AS 'Montant' FROM OPERATIONS WHERE(OPERATIONS.CAT='Transports' OR OPERATIONS.CAT ='Auto') AND (OPERATIONS.RUB ='Sncf' OR OPERATIONS.RUB='Péage' OR OPERATIONS.RUB='Carburant') AND OPERATIONS.DATE LIKE '2017%' GROUP BY NEWDAT,OPERATIONS.CAT, OPERATIONS.RUB

---

2017-06|Auto|Carburant|-94.04
2017-06|Transports|Sncf|-57.3
2017-07|Auto|Carburant|-89.83
2017-07|Auto|Péage|-53.2
2017-08|Auto|Carburant|-40
2017-08|Auto|Péage|-31.4
2017-08|Transports|Sncf|-181.7

peut-on faire un sous-total sur NEWDAT ? et un total général ? si compliqué je ferai un autre billet.
Bien cordialement

Bonjour,

les Common Table Expression (CTE) et les UNION sont là pour ça

par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- La CTE WITH C AS ( SELECT substr(DATE, 1, 7) AS NEWDAT, CAT,RUB,SUM(MONTANT) AS Montant FROM OPERATIONS WHERE(OPERATIONS.CAT='Transports' OR OPERATIONS.CAT ='Auto') AND (OPERATIONS.RUB ='Sncf' OR OPERATIONS.RUB='Péage' OR OPERATIONS.RUB='Carburant') AND OPERATIONS.DATE LIKE '2017%' GROUP BY NEWDAT,OPERATIONS.CAT, OPERATIONS.RUB) -- L'exploitation de la CTE SELECT NEWDAT FROM C UNION ALL SELECT NEWDAT,MAX('Total Mois'),MAX(''),SUM(montant) FROM C -- sous totaux par mois GROUP BY NEWDAT UNION ALL SELECT MAX('TOTAL'),MAX('2017'),MAX(''),SUM(Montant) FROM C -- total général

---

devrait donner quelque chose du genre

Citation:

---

2017-06|Auto|Carburant|-94.04
2017-06|Transports|Sncf|-57.3
2017-06|Total mois||-151.34
2017-07|Auto|Carburant|-89.83
2017-07|Auto|Péage|-53.2
2017-07|Total mois||143.03
2017-08|Auto|Carburant|-40
2017-08|Auto|Péage|-31.4
2017-08|Transports|Sncf|-181.7
2017-08|Total mois||253.10
TOTAL|2017|-547.47

---

si je ne me suis pas trompé dans mes calculs ;)
P.S. fait sans test (la flemme de faire un jeu d'essai) et basé sur mon expérience SQL avec un SGBD différent (Firebird) par contre si Firebird fourni un résultat trié il n'est pas évident que SQLite le fasse mais convertir le SQL fournissant le résultat en une CTE et faire une interrogation de celle -ci pour ordonner l'ensemble donnera la solution ;)

P.P.S une CTE recursive est peut être envisageable mais il est un peu trop tôt pour mon cerveau sans caféine ;)

Bonjour @SergioMaster

Merci pour ton aide et ces explications, j'ai un peu adapté le code pour que ça me sorte quelque-chose car j'avais ce message :
SELECTs to the left and right of UNION ALL do not have the same number of result columns: -- La CTE

et tes calculs son justes :)

Mais, en effet ce n'est pas trié avec SQLITE :(
Pièce jointe 306310

et refaire un CTE depuis un CTE je n'ai pas trop d'idée pour faire cela.. Les imbriquer ? ou remettre toutes les données dans une table en mémoire ?

Bien cordialement

Re,

le résultat répond à mon interrogation "par contre si Firebird fourni un résultat trié il n'est pas évident que SQLite le fasse"

Citation:

---

SELECTs to the left and right of UNION ALL do not have the same number of result columns: -- La CTE

---

:oops: je ne se sais pas où j'ai pu m....é :aie: une histoire de lignes avec les -- qui sont des commentaires peut être ?

Citation:

---

refaire un CTE depuis un CTE je n'ai pas trop d'idée pour faire cela.. Les imbriquer ?

---

Oui, cela se fait facilement

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

WITH C AS ( SELECT substr(DATE, 1, 7) AS NEWDAT, CAT,RUB,SUM(MONTANT) AS Montant FROM OPERATIONS WHERE(OPERATIONS.CAT='Transports' OR OPERATIONS.CAT ='Auto') AND (OPERATIONS.RUB ='Sncf' OR OPERATIONS.RUB='Péage' OR OPERATIONS.RUB='Carburant') AND OPERATIONS.DATE LIKE '2017%' GROUP BY NEWDAT,OPERATIONS.CAT, OPERATIONS.RUB), R AS (SELECT NEWDAT FROM C UNION ALL SELECT NEWDAT,MAX('Total Mois'),MAX(''),SUM(montant) FROM C GROUP BY NEWDAT UNION ALL SELECT MAX('TOTAL'),MAX('2017'),MAX(''),SUM(Montant) FROM C)   SELECT * FROM R ORDER BY NEWDAT

---

Bonsoir

Voila ça fonctionne :)

les commentaires n'étaient pas en cause dans le message d'erreur. J'ai mis les colonnes

Code:

---

R AS (SELECT NEWDAT,CAT, RUB, Montant FROM C

---

il n'y a plus de problèmes

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

-- avec CTE WITH C AS ( SELECT substr(DATE, 1, 7) AS NEWDAT, CAT,RUB,SUM(MONTANT) AS MNT FROM OPERATIONS WHERE(CAT='Transports' OR CAT ='Auto') AND (RUB ='Sncf' OR RUB='Péage' OR RUB='Carburant') AND NEWDAT >= '2017-06' GROUP BY NEWDAT,CAT, RUB), R AS (SELECT NEWDAT,CAT, RUB, MNT FROM C -- repeter les champs UNION ALL SELECT NEWDAT,MAX('Total Mois'),MAX(''),SUM(MNT) FROM C -- par année / mois GROUP BY NEWDAT UNION ALL SELECT MAX('TOTAL'),MAX('2017'),MAX(''),SUM(MNT) FROM C) -- sur l'année SELECT * FROM R ORDER BY NEWDAT

---

Pièce jointe 306548

Voila merci encore de ton aide

Bien cordialement