topic Re: Get All Sum in Application Development and Automation Discussions

Get All Sum

former_member557244 — Sat, 28 Mar 2020 06:35:10 GMT

Sir I need Sum of

Sales Order

Delivery Note

A/R Invoice

Incoming Payments

for a specific customer and within date range

To do this, I have following codes

SELECT T0."CardCode", max(T0."CardName") as party,
sum (T0."DocTotal") as sale_order_sum, 
sum( T1."DocTotal")  as delivery_sum,
sum(T2."DocTotal") as invoice_sum,
Sum(T3."CashSum") as incoming_payment_sum FROM ORDR T0
LEFT JOIN ODLN T1 ON T0."CardCode"=T1."CardCode"
LEFT JOIN OINV T2 ON T0."CardCode"=T2."CardCode"
LEFT JOIN ORCT T3 ON T0."CardCode"=T3."CardCode"
WHERE T0."CardCode" ='C2402115' 
AND T0."DocDate" Between  '2020-03-01' and '2020-03-07'
AND  T0."CANCELED" ='N' GROUP BY T0."CardCode"

Query runs without any error but displaying wrong result.

Please

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 06:39:09 GMT

If you can tell what is wrong.. what is expected and what you get?

Re: Get All Sum

former_member557244 — Sat, 28 Mar 2020 06:48:03 GMT

Sir here is a comparison of amount

I need Actual Amount

Here are separate queries showing actual amount

Sales Order

SELECT
T0."CardCode" AS "Numb",
max(T0."CardName") AS "Name",
sum(T0."DocTotal") AS "Recd"
FROM Ordr T0
WHERE T0."CardCode"='C2402115'
AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T0."CardCode"
ORDER BY T0."CardCode"

Delivery Note

SELECT
T0."CardCode" AS "Numb",
max(T0."CardName") AS "Name",
sum(T0."DocTotal") AS "Recd"
FROM Odln T0
WHERE T0."CardCode"='C2402115'
AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T0."CardCode"
ORDER BY T0."CardCode"

A/R Invoice

SELECT
T0."CardCode" AS "Numb",
max(T0."CardName") AS "Name",
sum(T0."DocTotal") AS "Recd"
FROM OINV T0
WHERE T0."CardCode"='C2402115'
AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T0."CardCode"
ORDER BY T0."CardCode"

Incoming Payments

SELECT
T0."CardCode" AS "Numb",
max(T0."CardName") AS "Name",
sum(T0."DocTotal") AS "Recd"
FROM OINV T0
WHERE T0."CardCode"='C2402115'
AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T0."CardCode"
ORDER BY T0."CardCode"

Please help me to join all above queries

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 07:31:48 GMT

Dear Tariq,

In individual query you did not compare cancelled = 'N'. Anyways, please use inner join instead of left join as below

SELECT T0."CardCode", max(T0."CardName") as party,
sum (T0."DocTotal") as sale_order_sum, 
sum( T1."DocTotal")  as delivery_sum,
sum(T2."DocTotal") as invoice_sum,
Sum(T3."CashSum") as incoming_payment_sum FROM ORDR T0
INNER JOIN ODLN T1 ON T0."CardCode"=T1."CardCode"
INNER JOIN OINV T2 ON T0."CardCode"=T2."CardCode"
INNER JOIN ORCT T3 ON T0."CardCode"=T3."CardCode"
WHERE T0."CardCode" ='C2402115' 
AND T0."DocDate" Between  '2020-03-01' and '2020-03-07'
AND  T0."CANCELED" ='N' GROUP BY T0."CardCode"

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 07:43:13 GMT

You have additional whereclause T0.cancelled = 'N' in the Query. (S2) against your individual query (S3)

Re: Get All Sum

former_member557244 — Sat, 28 Mar 2020 07:48:31 GMT

My respected Sir,

If I remove T0.cancelled='N' then there is still no difference in result

SELECT T0."CardCode", max(T0."CardName") as party,
sum (T0."DocTotal") as sale_order_sum, 
sum( T1."DocTotal")  as delivery_sum,
sum(T2."DocTotal") as invoice_sum,
Sum(T3."CashSum") as incoming_payment_sum FROM ORDR T0
INNER JOIN ODLN T1 ON T0."CardCode"=T1."CardCode"
inner JOIN OINV T2 ON T0."CardCode"=T2."CardCode"
inner JOIN ORCT T3 ON T0."CardCode"=T3."CardCode"
WHERE T0."CardCode" ='C2402115' 
AND T0."DocDate" Between  '2020-03-01' and '2020-03-07'
GROUP BY T0."CardCode"

Sir this is my first post in this community.

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 07:55:12 GMT

Dear Tariq, Please try with union as below:

SELECT T0."CardCode" AS "Numb", max(T0."CardName") AS "Name", sum(T0."DocTotal") AS "Recd", 0, 0
FROM Ordr T0
WHERE T0."CardCode"='C2402115'
AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T0."CardCode"
ORDER BY T0."CardCode"

UNION

SELECT T1."CardCode" AS "Numb", max(T0."CardName") AS "Name",0, sum(T1."DocTotal") AS "Recd"
FROM Odln T1
WHERE T1."CardCode"='C2402115'
AND T1."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T1."CardCode"
ORDER BY T1."CardCode"

UNION

SELECT T2."CardCode" AS "Numb", max(T0."CardName") AS "Name",0, 0, sum(T2."DocTotal") AS "Recd"
FROM OINV T2
WHERE T2."CardCode"='C2402115'
AND T2."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T2."CardCode"
ORDER BY T2."CardCode"

UNION

SELECT T3."CardCode" AS "Numb", max(T0."CardName") AS "Name",0, 0, 0, sum(T3."DocTotal") AS "Recd"
FROM ORCT T3
WHERE T3."CardCode"='C2402115'
AND T3."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T3."CardCode"
ORDER BY T3."CardCode"

Re: Get All Sum

former_member557244 — Sat, 28 Mar 2020 08:08:39 GMT

Sir here is an error in Toad

How to overcome this?

Please

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 08:28:49 GMT

Oh, sorry, remove the order by

and put order by at last query only

Re: Get All Sum

former_member557244 — Sat, 28 Mar 2020 09:37:33 GMT

Sir this time I have modified codes like this

select * from 
(SELECT T0."CardCode" AS "CardCode", max(T0."CardName") AS "Name",
sum(T0."DocTotal") AS "inv_pay", 0 as "del_pay"
FROM Ordr T0
WHERE T0."CardCode"='C2402115'
AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by T0."CardCode"
UNION all
SELECT T1."CardCode" AS "CardCode", max(T1."CardName") AS "Name",
0 as "inv_pay", sum(T1."DocTotal") AS "del_pay"
FROM Odln T1
WHERE T1."CardCode"='C2402115'
AND T1."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
group by "CardCode")
order by "CardCode"

The result looks like this

Now my question is how to display above result in one line, I mean group by CardCode

Please

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 10:29:58 GMT

Do not use UNION ALL .... use only UNION

Let me know

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 10:44:24 GMT

Dear Tariq,

In above query you used UNION ALL - which will not remove the duplicates. You remove that unionall and use only union and try, if not works out, then

So we can modify that query as subquery and select the final as below :

In this query I added only invoice and delivery, remaining you can add it

SUBQ = bottom query

select * from CARDCODE, NAME, SUM(INV_PAY), SUM(DEL_PAY)
FROM
    ( 
      SELECT T0."CardCode" AS "CardCode", max(T0."CardName") AS "Name", 
               sum(T0."DocTotal") AS "inv_pay", 0 as "del_pay"
      FROM Ordr T0
      WHERE T0."CardCode"='C2402115'
       AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
      group by T0."CardCode"
      UNION 
      SELECT T1."CardCode" AS "CardCode", max(T1."CardName") AS "Name", 
            0 as "inv_pay", sum(T1."DocTotal") AS "del_pay"
      FROM Odln T1
      WHERE T1."CardCode"='C2402115'
      AND T1."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
      group by "CardCode"
    )  AS "SUBQ"
order by "CardCode"
GROUP BY CARDCOE, NAME

Re: Get All Sum

former_member557244 — Sat, 28 Mar 2020 10:48:52 GMT

Sir this error is appearing.

Please

Re: Get All Sum

former_member557244 — Sat, 28 Mar 2020 11:00:04 GMT

Sir I have modified your query like this

select "SUBQ"."CardCode", max("SUBQ"."Name") as name, sum("SUBQ"."inv_pay")as inv_pay, sum("SUBQ"."del_pay")as del_pay
FROM 
( 
      SELECT T0."CardCode" AS "CardCode", max(T0."CardName") AS "Name", 
               sum(T0."DocTotal") AS "inv_pay", 0 as "del_pay"
      FROM Ordr T0
      WHERE T0."CardCode"='C2402115'
       AND T0."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
      group by T0."CardCode"
      UNION 
      SELECT T1."CardCode" AS "CardCode", max(T1."CardName") AS "Name", 
            0 as "inv_pay", sum(T1."DocTotal") AS "del_pay"
      FROM Odln T1
      WHERE T1."CardCode"='C2402115'
      AND T1."DocDate" BETWEEN '2020-03-01' AND '2020-03-07'
       group by T1."CardCode"
    )  AS "SUBQ"
    group by "CardCode"
order by "CardCode"

Now It works fine.

Thanks and warm regards.

Re: Get All Sum

venkateswaran_k — Sat, 28 Mar 2020 11:03:21 GMT

Try remove the bracket

Also did you try with union - in previous query ( remove uion all and put union only )