SQL Server 2005 добавить строку с итогами

У меня есть запрос SQL SERVER 2005, мне нужно добавить строку в конце с общей суммой некоторых столбцов. Я попытался использовать "сведение", но это требует группировки запроса. Я также пытался втиснуть все в подзапрос, но не смог. Может ли кто-нибудь дать мне какую-нибудь идею? столбцы должны суммироваться, все это «IMP». Спасибо!!

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

sum totals sql-server-2005
person martin.softpro    schedule 19.05.2016    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете использовать cte для повторного использования вашего запроса в операторе union:

;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
person Giorgi Nakeuri    schedule 19.05.2016