Getting results into 2 columns from one

[teachmecr]

Hi
if i have 2 tables (tbl A, tbl B). Tbl A has 2 columns (account,cash) and it has only one record. Tbl B has 3 columns(account,deposits,transactionCode) as well. They are joined by account and it has 20 records. In my view i wana retrieve everything from tblA which is only one record and from tblb i wana retrieve everything as well but i wana split deposits column into two columns as deposits_one and deposits_two based on transactionCodes but i dont want the cash from tblA to be repeated because i have to sum them at the end. my data is something like this

Sample
tbl A
account cash
123 100

tblb
account deposits trnansactionCode
123 50 5
123 75 10
123 200 5
123 80 10
123 90 5

The result should look something like this
account cash deposits1 deposits2
123 100 340(sum of deposits where code is 5) 155(for code 10)

how do i go about getting something like this. Thanks

 

[ablackwe]

Try the following. You will need to know what all the transaction codes are and have a colun for each one.

select	tblA.account,
	sum(case when tblB.transactionCode = 5 then tblB.deposits) as deposits_1,
	sum(case when tblB.transactionCode = 10 then tblB.deposits) as deposits_2
from	tblA INNER JOIN tblB on tblA.account = tblB.account
group by tblA.account

I hope this helps

Adam Blackwell
Information Analyst

http://www.workinglinks.co.uk/

 

[UpstateNYAdmin]

Hey teach, I think this is what u are looking for...basically you can use a case statement to differentiate between what should be summed in each column.

Select a.account,
a.cash,
deposits1 = SUM(CASE WHEN trnansactionCode = 5
then b.deposits Else 0 END)
deposits2 = SUM(CASE WHEN trnansactionCode = 10
then b.deposits Else 0 END)
From table_a a
Join table_b b
ON a.account = b.account
Group by a.account,
a.cash

 

[AlexCuse]

perhaps something like this?

select a.account, a.cash, 
sum(case b.transactioncode
when 5 then b.deposits else 0 end) as Deposits1,
sum(case b.transactioncode
when 10 then b.deposits else 0 end) as Deposits2
from tbla a inner join tblb b on 
a.account = b.account

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[AlexCuse]

lol

A wise man once said
"The only thing normal about database guys is their tables".

 

[AlexCuse]

hit submit too quickly on the last one. I meant lol forgot the group by

A wise man once said
"The only thing normal about database guys is their tables".

 

[teachmecr]

well this query doesnt give me the result im after. caz i have to do a sum on a.cash as well if i do tat it would give me 500 for cash which is not right. Is there any other way around this. because if just do a select with no sums it puts 100 for cash in each record repeated 5 times if somehow it just gives 100 once then i can sum it up and i wud get wat i want..thanks

 

[AlexCuse]

select a.account, sum(a.cash), 
sum(case b.transactioncode
when 5 then b.deposits else 0 end) as Deposits1,
sum(case b.transactioncode
when 10 then b.deposits else 0 end) as Deposits2
from tbla a inner join tblb b on 
a.account = b.account
group by a.account

I'm not sure what you are looking for as your sum of cash though... Are there multiple instances of each account in tblA?



A wise man once said
"The only thing normal about database guys is their tables".

 

[UpstateNYAdmin]

teach you need cash in the group by...

 

[teachmecr]

no tblA has just that one record..the way u have the query written above it wud give me this result
123 500 250 225
which is not right caz i have only onr record for cash (100) i wana get just 100.

 

[UpstateNYAdmin]

youre not supposed to sum the cash...group by acct and cash from table a

 

[UpstateNYAdmin]

Select a.account,
a.cash,
deposits1 = SUM(CASE WHEN trnansactionCode = 5
then b.deposits Else 0 END)
deposits2 = SUM(CASE WHEN trnansactionCode = 10
then b.deposits Else 0 END)
From table_a a
Join table_b b
ON a.account = b.account
Group by a.account,
a.cash

 

[teachmecr]

but thats what im saying i want to sum the cash eventually for my reports..thats the issue that i wud need to sum the cash.

 

[UpstateNYAdmin]

then try this...
Select a.account,
NEW_CASH = a.cash + SUM(b.deposits),
deposits1 = SUM(CASE WHEN trnansactionCode = 5
then b.deposits Else 0 END)
deposits2 = SUM(CASE WHEN trnansactionCode = 10
then b.deposits Else 0 END)
From table_a a
Join table_b b
ON a.account = b.account
Group by a.account,
a.cash

 

[teachmecr]

i think u r not understanding me. in my result set i need to get 100 for cash when i sum it up caz thats how it should be. the above query gives me 575 for cash which is not the rite figure

 

[AlexCuse]

If you want to sum the cash, then you would have to stop grouping by the account as there is only 1 record for each account in the table.

If you want to total the fields at the bottom use a union query.

If you want to sum cash + deposits1 + deposits2 then do what UpstateNYAdmin says. This would be better expressed as sum of cash and deposits rather than sum of cash though...

A wise man once said
"The only thing normal about database guys is their tables".

 

[AlexCuse]

Are you saying that you have something like this in tblA?

Account Cash
123 100
456 100

But that 'Cash' is really the sum of cash for all accounts?

I'm not sure I understand what you are talking about.

Alex


A wise man once said
"The only thing normal about database guys is their tables".

 

[gmmastros]

teachmecr,

UpstateNYAdmin's original query should work. I tested it and it appears to produce the correct output. In the code I show, I have created 2 table variables (which only exist in memory). I do this to show your sample data. You can copy/paste the entire query in to a query analyzer window and run it to see the results for yourself.

[green]-- Test tables to model sample data[/green]
Declare @tblA Table(Account Int, Cash Int)
Insert Into @tblA Values(123, 100)

Declare @tblb Table(Account int, Deposits int, trnansactionCode int)
Insert Into @tblB Values(123,50,5)
Insert Into @tblB Values(123,75,10)
Insert Into @tblB Values(123,200,5)
Insert Into @tblB Values(123,80,10)
Insert Into @tblB Values(123,90,5)

[green]-- The query![/green]
Select A.Account, A.Cash,
       Sum(Case When trnansactionCode = 5 Then Deposits Else 0 End) As Deposits1,
       Sum(Case When trnansactionCode = 10 Then Deposits Else 0 End) As Deposits2
From   [!]@tblA[/!] As A
       Inner Join [!]@tblB[/!] As B On A.Account = B.Account
Group By A.Account, A.Cash

If this query is acceptable, simply remove the top part and change the table names (appearing in [!]Red[/!]) with your actual table names.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[UpstateNYAdmin]

I dont think we are understanding you. If the answer is 100 then you need to use the a.cash with the group by, NO SUM INVOLVED, its not correct to sum them if that is the current cash balance after the deposits.

 

[ESquared]

SELECT
   A.Account,
   A.Cash,
   X.Deposits1,
   X.Deposits2
FROM
   TblA A
   INNER JOIN (
      SELECT Account,
         Deposits1 = Sum(CASE TransactionCode WHEN 5 THEN Deposits ELSE 0 END),
         Deposits2 = Sum(CASE TransactionCode WHEN 10 THEN Deposits ELSE 0 END)
      FROM TblB
      GROUP BY Account
   ) X ON A.Account = X.Account

If you have a changing number of transaction codes:

DECLARE @SQL varchar(8000)
SET @SQL = ''
SELECT @SQL = @SQL + '         Deposits' + Convert(varchar(12), TransactionCode) 
	+ ' = Sum(CASE TransactionCode WHEN ' + Convert(varchar(12), TransactionCode)
	+ ' THEN Deposits ELSE 0 END)'
FROM (
	SELECT DISTINCT TransactionCode FROM TblB -- Better, use TransactionCode lookup table
) X

SET @SQL = 
'SELECT
   A.Account,
   A.Cash,
   X.Deposits1,
   X.Deposits2
FROM
   TblA A
   INNER JOIN (
      SELECT Account'
+ @SQL
+ '      FROM TblB
      GROUP BY Account
   ) X ON A.Account = X.Account'

EXEC (@SQL)

If you really want the columns to be Deposits1, Deposits2, and so on, declare a simple counting variable and add a @ColNum = @ColNum + 1 to the equation.

Or if you're using SQL Server 2005, just use the PIVOT keyword (I'm not up to speed on this, so you're on your own).