Skip to Content
author's profile photo
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

Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Best Answer
    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

    Add comment
    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