Get All Sum

[former_member557244]

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

[former_member557244]

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

[venkateswaran_k]

Hi

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

[former_member557244]

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

[venkateswaran_k]

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"

[venkateswaran_k]

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

[former_member557244]

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.

[venkateswaran_k]

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"

[former_member557244]

Sir here is an error in Toad

How to overcome this?

Please

[venkateswaran_k]

Oh, sorry, remove the order by

and put order by at last query only

[former_member557244]

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

[venkateswaran_k]

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

Let me know

[venkateswaran_k]

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

[venkateswaran_k]

Try remove the bracket

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

[former_member557244]

Sir this error is appearing.

Please

[former_member557244]

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.