Calculation from UNION query

[Former Member]

Hi Experts,

I have the following query:

SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244') AND T1.RefDate >= [%0] AND T1.RefDate <= [%1]

UNION

SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account = '_SYS00000000053' AND T1.RefDate >= [%0] AND T1.RefDate <= [%1]

I need to add a new Union to show the First 'select' result minus the Second 'select' result. Then I need to also show this as a persentage in another Union underneath.

Any help would be appreciated.

Marli

[Former Member · Accepted solution]

Hi Marli,

Try this:

SELECT 'Revenue' as [Type],
(SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244')
 
UNION
 
SELECT 'COGS' as [Type], SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account = '_SYS00000000053'
 
UNION
 
select 'GP' as [Type],
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244'))
+
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000053'))
) as 'Production'
 
UNION
 
select 'GP&' as [Type],
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000053'))
/ 
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244'))
) as 'Production'

Thanks,

Gordon

Edited by: Gordon Du on Jul 9, 2010 11:25 AM

[Former Member · Accepted solution]

Gordon,

Thanks for your help.

When i run this I get the red line error on 'Incorrect Syntax near 'AS'. Even when I only do the first select I get this error.

Marli

[Former Member · Accepted solution]

I have updated the query above. Try again.

[Former Member · Accepted solution]

Gordon,

Thanks again, but I still get a error on the 'AS' on that query.

Marli

[JesperB1 · Accepted solution]

Hi Marli,

I guess you are getting the error when running it is SQL? Correct?

If you double click on the error it should indicate exactly where the error is, on which line.

Either way, maybe try to use 'Type' instead of [Type].

Let us know.

Jesper

[Former Member · Accepted solution]

Jesper,

Thanks for the SQL advise. This error was because of brackets that was not closed in the 1st select statement.

SELECT 'Revenue' as [Type],
(SUM(T0.SYSDeb - T0.SYSCred)) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244')
 
UNION
 
SELECT 'COGS' as [Type], SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account = '_SYS00000000053'
 
UNION
 
select 'GP' as [Type],
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244'))
+
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000053'))
) as 'Production'
 
UNION
 
select 'GP&' as [Type],
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000053'))
/ 
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244'))
) as 'Production'

[JesperB1]

Hi Marli,

From the description thios might work.

Let us know how it goes.

SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244') AND T1.RefDate >= [%0] AND T1.RefDate <= [%1]
 
UNION
 
SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account = '_SYS00000000053' AND T1.RefDate >= [%0] AND T1.RefDate <= [%1]

UNION 

select
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244') AND T1.RefDate >= [%0] AND T1.RefDate <= [%1])
-
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244') AND T1.RefDate >= [%0] AND T1.RefDate <= [%1])
) as 'Production'

UNION

select 
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244') AND T1.RefDate >= [%0] AND T1.RefDate <= [%1])
/
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244') AND T1.RefDate >= [%0] AND T1.RefDate <= [%1])
) as 'Production'

Again, if this is not it, let us know. It might be that you need to change the last 2 queries around, might be deviding by the incorrect query, but only you will know that.

Jesper

[Former Member]

Jesper,

Thanks for the help.

I tested this and found that if I run one the first 2 statements I get the correct totals, but as soon as I add the 3rd and 4th statements the 1st two amounts also change. I have played around with this and made small changes. In this new query, I get the right totals, but the order in which they display is not the order of the Unions.

SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244')

UNION

SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account = '_SYS00000000053'

UNION

select
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244'))
+
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000053'))
) as 'Production'

UNION

select 
(
(SELECT SUM(T0.SYSDeb - T0.SYSCred)
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000053'))
/ 
(SELECT SUM(T0.SYSDeb - T0.SYSCred) 
FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244'))
) as 'Production'

Also the last UNION need to multiply by 100 to get the persentage.

Thanks again for your help.

Marli

[Former Member]

Jesper,

Also if I want to display a name next to all the unions to show what this amount is (in addition to having this as a column header), how will I go about that?

The first Union should be Revenue, the second should be COGS, the 3rd GP and the last GP%.

Thanks,

Marli

[Former Member]

Hi Marli,

Is Union a must for your query? Do you need the difference and percentage column wise?

Thanks,

Gordon