Creating "Conditional" Sums in Query
Creating "Conditional" Sums in Query
(OP)
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.
<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.
Talk To Other Members
RE: Creating "Conditional" Sums in Query
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
RE: Creating "Conditional" Sums in Query
I was wondering if yuo had any sucess with your issue?
Regards,
Tom
RE: Creating "Conditional" Sums in Query
RE: Creating "Conditional" Sums in Query