Search code examples
sql-server-2005sum

sql server 2005 add row with totals


I have a SQL SERVER 2005 query , I need to add a row at the end , with the total amounts of some of the columns . I have tried using ' rollup ' , but that requires me to group the query . I have also tried to cram everything into a subquery , but I could not . Could someone give me some idea? the columns have to totalize , are all the 'IMP'. Thanks!!

SELECT C.INS,C.TIM,C.OIM,C.SIM,C.NIM,C.FEC AS FECHA_FACT,C.CCC,C.SUC,C.NRO,C.IMP AS TOTAL_FACT,
ISNULL(C1.IMP,0) AS PAGO_INST,ISNULL(C2.IMP,0) AS NOTA_CREDITO,ISNULL(C3.IMP,0) AS RET_GANANCIAS,
ISNULL(C4.IMP,0) AS RET_ING_BRU,ISNULL(C6.IMP,0) AS RET_IVA,   ISNULL(C5.IMP,0) AS DEBITO_FECLIBA,
ISNULL(C7.IMP,0) AS RET_SUSS
,(C.IMP + ISNULL(C1.IMP,0) + ISNULL(C2.IMP,0) + ISNULL(C3.IMP,0) + ISNULL(C4.IMP,0) + ISNULL(C5.IMP,0)+ ISNULL(C6.IMP,0) + ISNULL(C7.IMP,0)) AS SALDO
FROM CLICTA C 
LEFT JOIN CLICTA C1 ON C.INS = C1.INS AND C1.CCC = 'PI' AND C.NIM = C1.NIM
LEFT JOIN CLICTA C2 ON C.INS = C2.INS AND C2.CCC = 'cp' AND C.NIM = C2.NIM
LEFT JOIN CLICTA C3 ON C.INS = C3.INS AND C3.CCC = 'R1' AND C.NIM = C3.NIM
LEFT JOIN CLICTA C4 ON C.INS = C4.INS AND C4.CCC = 'R2' AND C.NIM = C4.NIM
LEFT JOIN CLICTA C5 ON C.INS = C5.INS AND C5.CCC = 'R5' AND C.NIM = C5.NIM
LEFT JOIN CLICTA C6 ON C.INS = C6.INS AND C6.CCC = 'R6' AND C.NIM = C6.NIM
LEFT JOIN CLICTA C7 ON C.INS = C7.INS AND C7.CCC = 'S1' AND C.NIM = C7.NIM
WHERE C.INS BETWEEN '011I' AND '011I'  --INSTITUCION         
AND C.TIM BETWEEN 'fp' AND 'fp'     --TIPO COMPROBANTE
AND C.OIM BETWEEN '' AND ''         --ORIGEN
AND C.SIM BETWEEN '100' AND '100'   --SUCURSAL        
AND C.NIM BETWEEN '4370' AND '4395'  --DESDE HASTA COMPROBANTE            
AND C.CCC = 'fp' --TIPO COMPROBANTE
AND C.IMP <> 0
ORDER BY C.NIM

Solution

  • You can use a cte to reuse your query in union statement:

    ;with cte as(current quety)
    select *, 1 as ordering from cte
    union
    select ..., null, sum(), null ,..., 2 as ordering from cte
    group by ...
    order by ordering