how to sum a column depending of another colum into different vars?

Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example

DECLARE @.Initial decimal(18,2), @.incomings decimal(18,2), @.outgoings decimal(18,2)

SELECT
@.initial = CASE WHEN type = 1 THEN SUM(amount) END,
@.incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@.outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount

What I am trying to do is to sum all of the incomings transactions into @.incomings, all of the outgoing transactions into @.outgoings and the initial transaction into @.initial where
The incoming transactions is type 2,
outgoing transactions is type 3

Thanks for the help

@.puy

Hi, this should work for you:

SELECT
@.initial = SUM(CASE WHEN type = 1 THEN amount ELSE 0 END),
@.incomings = SUM(CASE WHEN type = 2 THEN amount ELSE 0 END),
@.outgoings = SUM(CASE WHEN type = 3 THEN amount ELSE 0 END),
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi, sorry for the late answer...

Your query just returns almost the same as mine, it just return the value for the initial with the difference that mine return null the other, yours return 0.

Here it is the query I ran and the results

declare @.initial decimal(18,2), @.incomings decimal(18,2), @.outgoings decimal(18,2)

SELECT
@.initial = SUM(CASE WHEN tipo = 1 THEN monto ELSE 0 END),
@.incomings = SUM(CASE WHEN tipo = 2 THEN monto ELSE 0 END),
@.outgoings = SUM(CASE WHEN tipo = 3 THEN monto ELSE 0 END)
FROM Caja
WHERE fecha = '05/14/2006' AND estacion = 'apuyinc'
GROUP BY tipo, monto
select @.initial, @.incomings, @.outgoings

select tipo,monto,fecha,estacion from caja where fecha = '05-14-2006' and estacion = 'apuyinc'

initial incomings outgoings
-- -- --
15000.00 .00 .00

(1 row(s) affected)

tipo monto fecha estacion
- --
1 15000.00 2006-05-14 00:00:00 APUYINC
2 3480.00 2006-05-14 00:00:00 APUYINC
2 3140.00 2006-05-14 00:00:00 APUYINC
2 11010.00 2006-05-14 00:00:00 APUYINC
7 -5000.00 2006-05-14 00:00:00 APUYINC

(5 row(s) affected)

As you can see, it should retrieve 17630 when tipo = 2...

Any other suggestion will be highly appreciate...

thanks,

@.puyinc

P.S. Sorry for changing the language, these were the original column values ;)

|||Yes, we messed up with the variables / or the grouping, if you just want to have the sums in common, not grouped by any column this should be the following:

DROP TABLE #someTable

CREATE TABLE #SomeTable

(

tipo int,

monto int,

fecha datetime,

estacion varchar(100)

)

SELECT * FROM #SomeTable

INSERT INTO #SomeTable

SELECT 1,15000,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 2,3480,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 2,3140,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 2,11010,'2006-05-14 00:00:00','APUYINC'

INSERT INTO #SomeTable

SELECT 7,-5000,'2006-05-14 00:00:00','APUYINC'

SELECT

@.initial = SUM(CASE WHEN tipo = 1 THEN monto ELSE 0 END),
@.incomings = SUM(CASE WHEN tipo = 2 THEN monto ELSE 0 END),
@.outgoings = SUM(CASE WHEN tipo = 3 THEN monto ELSE 0 END)

FROM #SomeTable

WHERE fecha = '05/14/2006' AND estacion = 'apuyinc'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you for the help, it works now.

I added the "group by" clause in some moment that gave me a syntax error and force me to add it. But now it looks like it is not necesary.