Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating "Conditional" Sums in Query 1

Status
Not open for further replies.
byrne1

byrne1

Programmer
Aug 7, 2001
415
US
I have a table that has the following fields:

<customernum>
<transtype>
<tickettotal>

I want to get a sum of sales grouped by <customernum>. Should be simple enough, except that the <tickettotal> field is always stored as a positive value, even for credits (as indicated by <transtype>="CRD"). What I want is the total sum of sales minus the sum of credits by customer.

Is there a way I can put this in a SQL query and have my server do the work, or do I have to do this on the client end?

BTW, I'm running Pervasive.SQL 8.6.
 
Sort by date Sort by votes
Hi,

You could try using the IF statement with your select. The IF command works a bit like the CASE command in SQL Server.

SELECT
SUM(IF (TransType = 'CRD',
TicketTotal * -1,
TicketTotal))
FROM <NameofyourTable>

Hope this helps,
Tom
 
Upvote 0 Downvote
Hi,

I was wondering if yuo had any sucess with your issue?

Regards,
Tom

 
Upvote 0 Downvote
Yeah, sorry I didn't respond soooner. This formula worked flawlessly! Thank you!
 
Upvote 0 Downvote
You're very welcome! And thanks for the star, much appreciated.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
How to Execute Create View code in Actian Zen?
Replies
1
Views
527
mirtheil
mirtheil
michellepace
  • Locked
  • Question
Left Outer join returning &quot;Unknown Error -19&quot;
Replies
6
Views
592
mirtheil
mirtheil
profoundhypnotic
  • Locked
  • Question
How to read a csv file in Pervasive?
Replies
1
Views
583
mirtheil
mirtheil
ianbtheog
  • Locked
  • Question
Connection Manager is not running at the specified port number - Pervasive 13.31.014 - Intermittent
Replies
2
Views
548
ianbtheog
ianbtheog
Alvin Chong
  • Locked
  • Question
Invisible Space In my PSQL database
Replies
7
Views
608
Alvin Chong
Alvin Chong

Part and Inventory Search

Sponsor

Back
Top