Creating "Conditional" Sums in Query 1

[byrne1]

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.

 

[TomKane]

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

 

[TomKane]

Hi,

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

Regards,
Tom

 

[byrne1]

Yeah, sorry I didn't respond soooner. This formula worked flawlessly! Thank you!

 

[TomKane]

You're very welcome! And thanks for the star, much appreciated.