SubConsulta separates me sql sever data

Question

SubConsulta separates me sql sever data

Navigation

#1 by (4 votes)
#2 by (-1 votes)

0

I have my query:

select
(select SUM(cco.Valor) from Temp t where t.cod = cco.cod and cco.costo = 1 )as uno,
(select SUM(cco.Valor) from Temp t where t.cod = cco.cod and cco.costo = 2 )as dos,
(select SUM(cco.Valor) from Temp t where t.cod = cco.cod and cco.costo = 3 )as tres,
(select SUM(cco.Valor) from Temp t where t.cod = cco.cod and cco.costo = 4 )as cuatro

from  Cuenta cco
group by
cco.cod,
cco.costo

that is dynamic, by that I mean that within my subqueries I can do more filters, such as date, range of ages, everything is within the subquery

sql sql-server

asked by 03.11.2017 в 15:05

source

2 answers

4

There are several points of improvement in your code. The most important is that you are grouping by the costo column, when in fact it is not needed. On the other hand, there is no need to make a subquery for each column, for this there is SUM :

SELECT  cod,
        SUM(CASE WHEN costo = 1 THEN valor END) uno,
        SUM(CASE WHEN costo = 2 THEN valor END) dos,
        SUM(CASE WHEN costo = 3 THEN valor END) tres,
        SUM(CASE WHEN costo = 4 THEN valor END) cuatro
FROM Cuenta
GROUP BY cod
;

answered by 03.11.2017 в 15:09

Problems with the Bootstrap carousel fopen (): failed to open stream: permission denied in

score -1 · Accepted Answer

if you want to do it your way ...

select 
(select SUM(cc.Valor) from Cuenta cc where cc.cod = cco.cod and cc.costo = 1  ) as uno,
(select SUM(cc.Valor) from Cuenta cc where cc.cod = cco.cod and cc.costo = 2 ) as dos,
(select SUM(cc.Valor) from Cuenta cc where cc.cod = cco.cod and cc.costo = 3) as tres,
(select SUM(cc.Valor) from Cuenta cc where cc.cod = cco.cod and cc.costo = 4) as cuatro

from  Cuenta cco
group by 
cco.cod

that would be the way ..