# Thread: Case statement? or is it easier

1. Registered User
Join Date
Nov 2012
Posts
4

## Unanswered: Case statement? or is it easier

I am pulling data from a very simple database. It is for cash dispursment and collection. The table stores the data all in prime numbers, with a field differentiating between transaction type (deposit or dispense).

I need to build a simple report to show how much the user dropped in comparison to what was dispensed, but i've hit a brain block.

here's an example of the table:

12 Karen Dispense 1500
12 Karen deposit 1500
26 Tony Dispense 600
26 Tony Deposit 605

What i need is to get:

12 Karen 0
26 Tony 5

I keep trying to overcomplicate it, but i'm sure you are much smarter then me and have a much simpler way to do this that i am missing....

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
you're right, CASE expression

along with SUM and GROUP BY

when's this assignment due?

3. Registered User
Join Date
Nov 2012
Posts
4
I apologize if this is a dumb question, but how do i get the 'Dispense' lines to be negative? That is the piece I am stuck at.....

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
show me how you handle the deposit lines, and i'll show you where to stick the minus sign

5. Registered User
Join Date
Nov 2012
Posts
4
I think I got it!!!

CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
where's the SUM()? and why divide by 100?

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Originally Posted by belmontiz
The table stores the data all in prime numbers
You lost me there, bro'.....

But what you need is a CASE statement to determine whether to multiply the amount by -1, and then just sum up the values.

8. Registered User
Join Date
Nov 2012
Posts
4
I should probably post this as a new thread...but i've gotten pretty close. I'm having an issue with having 2 joins and it messing up the Sum. Here is the query as i wrote it out:

select t.userUID, u.FirstName, u.LastName, b.bankname,
SUM(
CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop
join kctb_admin_users u on t.userUID = u.userUID
join kctb_admin_banknames b on t.banknameUID = b.banknameUID
where t.dtstamp > '11-14-2012' and t.dtstamp < '11-15-2012'
group by t.userUID, u.FirstName, u.LastName, b.bankname
order by t.userUID, u.FirstName, u.LastName, b.bankname

But it's giving me the wrong number in the sum. It seems to be doubling up the negative amount. So then I was trying to do something like this....

select t.userUID, u.FirstName, u.LastName,
(Select b.bankname from kctb_admin_banknames b where t.banknameUID = b.banknameUID) ,
SUM(
CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop
join kctb_admin_users u on t.userUID = u.userUID
where t.dtstamp > '11-14-2012' and t.dtstamp < '11-15-2012'
group by t.userUID, u.FirstName, u.LastName, t.banknameUID
order by t.userUID, u.FirstName, u.LastName, t.banknameUID

But it's obviously not working. I'm not sure the best way to put the imbedded Select statement in there with a join, and get it to group/sum....

thoughts?

9. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
if you're tracking down incorrect totals, try splitting off the different ones separately

change this --

SUM(
CASE
when t.transactiontype = 'DEPOSIT' then t.total
else t.total*-1
END)/100 as TotalDrop

to this --
Code:
```, SUM( CASE WHEN t.transactiontype = 'DEPOSIT'
THEN t.total
ELSE NULL END) / 100 AS total_dep
, SUM( CASE WHEN t.transactiontype = 'DISPENSE'
THEN - t.total
ELSE NULL END) / 100 AS total_disp
, SUM( t.total ) AS total_total```

10. Registered User
Join Date
Nov 2012
Location
Russia. Kursk
Posts
3
select t.userUID, (sum(t.total) - 2*sum(tn.total))/100
from
left join