× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I agree that case may be used to perform such tasks, but after significant testing this can be performed much, much faster, if the only calculation occurring in the query is the Sum Aggregate Function.

So, if you have a table as such,

Create Table QTEMP.SUMME (ITEM, ITEMGRP, Value) As
(Values
('AAAAAA', 'A', 100.00),
('AAAAAA', 'B', 150.00)) With Data

The statement to summary the data would look like this.

Select
ITEM,
Sum(NEWVALUE) As NEWVALUE
From
QTEMP.SUMME As A
Left Join Lateral
(Values ('A', Value),
('B', -Value)) As B (ITEMGRP, NEWVALUE)
On
A.ITEMGRP = B.ITEMGRP
Group By
ITEM

Returns a summary amount for item 'AAAAA' = -50.00.


Put both statements into VE and see which one is faster.


Jim










Date: Fri, 30 Nov 2012 19:54:54 -0500
From: lennon_s_j@xxxxxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL SUM(fieldname) - can I use CASE??

Definitely. Eric gave you the answer.

A Case expression can be used almost anywhere a value is needed. This
is extremely useful and if you want to explore it more, check out this
article:

http://www.mcpressonline.com/sql/the-case-for-sql-case.html

(Gratuitous plug on my part).

Sam

On 11/30/2012 5:41 PM, Stone, Joel wrote:
Can I sum a fieldname using the CASE stmt as follows:

when the item-group = 'A', sum as is;
when the item-group = 'B', multiply by -1 and then sum?


How is this best accomplished?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.