Bonjour à tous,

Soit la requête SQLite suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
SELECT  '0' bigTot, '1' orderline, substr(c.num, 0, 2) as classe, substr(c.num, 0, 4) as sousclasse, c.num as compte, c.libelle as libCompte, COALESCE(SUM(b.debit), 0) as debitTot, COALESCE(SUM(b.credit), 0) as creditTot,
CASE WHEN COALESCE(SUM(b.debit), 0) >= COALESCE(SUM(b.credit), 0) THEN printf('%.2f', COALESCE(SUM(b.debit), 0) - COALESCE(SUM(b.credit), 0)) ELSE '0' END as soldeDebit,
CASE WHEN COALESCE(SUM(b.credit), 0) >= COALESCE(SUM(b.debit), 0) THEN printf('%.2f', COALESCE(SUM(b.credit), 0) - COALESCE(SUM(b.debit), 0)) ELSE '0' END as soldeCredit
FROM COMPTA_HEAD_2014 h JOIN COMPTA_BODY_2014 b
ON h.id = b.headEntry_id
JOIN COMPTA_COMPTE_PCE c
ON c.id = b.pce_id
WHERE c.num LIKE '401%'
GROUP BY c.num
 
UNION
SELECT '0' bigTot, '2' orderline, substr(c.num, 0, 2) as classe, substr(c.num, 0, 4) as sousclasse, '' as compte, 'TOTAL CLASSE ' || substr(c.num, 0, 2) as libCompte, COALESCE(SUM(b.debit), 0) as debitTot, COALESCE(SUM(b.credit), 0) as creditTot,
'' as soldeDebit,
'' as soldeCredit
FROM COMPTA_HEAD_2014 h JOIN COMPTA_BODY_2014 b
ON h.id = b.headEntry_id
JOIN COMPTA_COMPTE_PCE c
ON c.id = b.pce_id
WHERE c.num LIKE '401%'
GROUP BY classe
 
UNION
SELECT '1' bigTot, '3' orderline, substr(c.num, 0, 2) as classe, substr(c.num, 0, 4) as sousclasse, '' as compte, 'SOLDE BALANCE' as libCompte, 
CASE WHEN COALESCE(SUM(b.debit), 0) >= COALESCE(SUM(b.credit), 0) THEN printf('%.2f', COALESCE(SUM(b.debit), 0) - COALESCE(SUM(b.credit), 0)) ELSE '0' END as debitTot,
CASE WHEN COALESCE(SUM(b.credit), 0) >= COALESCE(SUM(b.debit), 0) THEN printf('%.2f', COALESCE(SUM(b.credit), 0) - COALESCE(SUM(b.debit), 0)) ELSE '0' END as creditTot,
'' as soldeDebit,
'' as soldeCredit
FROM COMPTA_HEAD_2014 h JOIN COMPTA_BODY_2014 b
ON h.id = b.headEntry_id
JOIN COMPTA_COMPTE_PCE c
ON c.id = b.pce_id
WHERE c.num LIKE '401%'
GROUP BY bigTot
 
ORDER BY bigTot, classe,  orderline
Dans la requête où orderline est à 2, j'aimerai pouvoir additionner tous les soldeDebit des lignes où orderline est à 1 et idem avec les soldeCredit.

J'aurai aimé passer par une variable que j'aurai définie et incrémentée à chaque orderline "1", mais SQLite ne semble pas supporter ceci.

Auriez-vous une idée pour faire ce que je souhaite ?

D'avance merci.

++


J