 Former Member

# How To Calculate Tables Across SELECT Statements

Dear Friends,

Does anybody know the secret to calculating tables across SELECT statements? For example, if I need to divide T0 in the first SELECT by T3 in the second SELECT?

This is the actual query i'm working on:

SELECT sum(T0.[Quantity]) as 'INV KG', sum(T0.[LineTotal]) as 'INV JPY', (sum(T0.[LineTotal]) / sum(T0.[Quantity])) as 'INV JPY/KG', NULL as 'SO KG', NULL as 'SO JPY' FROM INV1 T0 inner join OINV T3 on T0.[DocEntry]

= T3.[DocEntry] WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <= [%1] AND T3.[CANCELED] = 'N' AND T0.[Quantity] > .001 union all

SELECT NULL as 'INV KG', NULL as 'INV JPY', NULL as 'INV JPY/KG', sum(T1.[Quantity]) as 'SO KG', sum(T1.[LineTotal]) as 'SO JPY' FROM RDR1 T1 inner join ORDR T2 on T1.[DocEntry]

= T2.[DocEntry] WHERE T2.[DocDate] >=[%0] and T2.[DocDate] <= [%1] AND T2.[CANCELED] = 'N'

In the second SELECT, i need to be able to perform this calculation, but i get an error:

(sum(T0.[LineTotal]) / sum(T0.[Quantity]) * sum(T1.[Quantity]))

Any help or advice is greatly appreciated.

Thank you,

Justice

10|10000 characters needed characters exceeded

Posted on Apr 28, 2011 at 05:45 AM

Try this

```Declare @from as datetime
Declare @to as datetime
Set @from = /* Select min(T5.DocDate) from OINM T5 where T5.DocDate = */ '[%0]'
Set @to = /* Select max(T6.DocDate) from OINM T6 where T6.DocDate = */ '[%1]'
Select *, (A.INVKG) / (A.SOKG) as 'Your Text' from (SELECT sum(T0.Quantity) as 'INV KG', sum(T0.LineTotal) as 'INV JPY', (sum(T0.LineTotal) / sum(T0.Quantity)) [INVKG], NULL as 'SO KG', NULL as 'SO JPY' FROM INV1 T0 inner join OINV T3 on T0.DocEntry
= T3.DocEntry WHERE T0.DocDate >=@from and T0.DocDate <=@to AND T3.CANCELED = 'N' AND T0.Quantity > .001 union all
SELECT NULL as 'INV KG', NULL as 'INV JPY', NULL as 'INV JPY/KG', sum(T1.Quantity) [SOKG], sum(T1.LineTotal) as 'SO JPY' FROM RDR1 T1 inner join ORDR T2 on T1.DocEntry
= T2.DocEntry WHERE T2.DocDate >=@from and T2.DocDate <=@to] AND T2.CANCELED = 'N') A```

Regards,

Bala

10|10000 characters needed characters exceeded
• Former Member Former Member

Hi Bala,

I changed some of your code a bit, seems to be working now.

Again, not sure how you made this, but it's good.

Is this standard SQL or.....?

This worked, note main difference is (sum(A.INVKG) * sum(A.SOKG)) + sum(A.[INV JPY]) as 'Estimated Turnover'

```Declare @from as datetime
Declare @to as datetime
Set @from = /* Select min(T5.DocDate) from OINM T5 where T5.DocDate = */ '[%0]'
Set @to = /* Select max(T6.DocDate) from OINM T6 where T6.DocDate = */ '[%1]'
Select sum(A.[INV KG]) [INV KG], sum(A.[INV JPY]) [INV JPY], sum(A.[INVKG]) [INV JPY / KG],sum(A.[SOKG]) [Open SO KG], (sum(A.INVKG) * sum(A.SOKG)) + sum(A.[INV JPY]) as 'Estimated Turnover' from (SELECT sum(T0.Quantity) as 'INV KG', sum(T0.LineTotal) as 'INV JPY', (sum(T0.LineTotal) / sum(T0.Quantity)) [INVKG], 0 as [SOKG], NULL as 'SO JPY' FROM INV1 T0 inner join OINV T3 on T0.DocEntry
= T3.DocEntry WHERE T0.DocDate >=@from and T0.DocDate <=@to AND T3.CANCELED = 'N' AND T0.Quantity > .001 union all
SELECT NULL as 'INV KG', NULL as 'INV JPY', 0 as [INVKG], sum(T1.Quantity) [SOKG], sum(T1.LineTotal) as 'SO JPY' FROM RDR1 T1 inner join ORDR T2 on T1.DocEntry
= T2.DocEntry WHERE T2.DocDate >=@from and T2.DocDate <=@to AND T2.[DocStatus] = 'O' AND T2.CANCELED = 'N')```

A

Many thanks,

Justice