Summing the Balances together

[macdonald_ngowi]

Hi guys,

I have this query that gets the outstanding balances per business partner (customers) and orders them in descending. Where I'm stuck is how to sum all these balances together to give me one total value at the end. The following is the query:

SELECT T0.[CardName], case when T0.[Currency]   = 'USD' THEN  T0.[BalanceFC] else  T0.[BalanceSys] end AS 'Balance' FROM OCRD T0
 WHERE T0.[CardType] ='C' and T0.[BalanceSys] >'0' and T0.[BalanceFC] >'0' ORDER BY  T0.[BalanceSys] DESC

I tried to use SUM() but it just copies the values to a new field and doesn't give me what I'm expecting.

Any help will much be appreciated. Thanks.

- Macdonald

[Johan_H · Accepted solution]

Hi Macdonald,

A couple of things:

• Why are you using the case statement? Why not always use T0.[BalanceSys], or is the system currency not USD? With the case statement you are showing one of two possible fields, but then in the ORDER BY clause, you order it specifically by the T0.[BalanceSys] field.
• You could use a simple UNION ALL:
  SELECT T0.[CardName]
              , case
                 when T0.[Currency]   = 'USD' THEN  T0.[BalanceFC]
                 else  T0.[BalanceSys]
                end AS 'Balance'
  FROM OCRD T0 
  WHERE T0.[CardType] ='C' and T0.[BalanceSys] >'0' and T0.[BalanceFC] >'0'
  UNION ALL
  SELECT 'Total'
              , SUM(case
                         when T0.[Currency]   = 'USD' THEN  T0.[BalanceFC]
                         else  T0.[BalanceSys]
                        end) AS 'Balance'
  FROM OCRD T0 
  WHERE T0.[CardType] ='C' and T0.[BalanceSys] >'0' and T0.[BalanceFC] >'0'
  ORDER BY  Balance DESC
  However, because you are ordering in descending order, the total would always be the first row as it is the largest value.

Regards,

Johan

[macdonald_ngowi]

Thank you very much, I can work with this.