October 23, 2007 at 6:59 am
I am trying to summarize the result of union queries but keep getting syntax error
I have three tables A, B , C With 4 column that I want to rollup
this works but give me 3 rows of data I only want one
select
sum(a1) d1, sum(a2) d2, sum(a3) d3, sum(a4) d4
from a
union
sum(b1) d1, sum(b2) d2, sum(b3) d3, sum(a4) d4
from a
union
select sum(c1) d1, sum(c2) d2, sum(c3) d3 , sum(c4) d4
How do I roll them up? this does not work
Select sum(d1), sum(d2) sum(d3), sum(d4)
from
(select
sum(a1) d1, sum(a2) d2, sum(a3) d3, sum(a4) d4
from a
union
sum(b1) d1, sum(b2) d2, sum(b3) d3, sum(a4) d4
from a
union
select sum(c1) d1, sum(c2) d2, sum(c3) d3 , sum(c4) d4
)
October 23, 2007 at 7:04 am
WITH
cte_tbl
AS ( SELECT
a1, a2, a3, a4
FROM
a
UNION
SELECT
b1, b2, b3, b4
FROM
B
UNION
SELECT
c1, c2, c3, c4
FROM
C)
SELECT
SUM() AS d1
,SUM() AS d2
,SUM() AS d3
,SUM() AS d4
FROM
cte_tbl
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 23, 2007 at 4:41 pm
The query you provided will work, with a few minor fixes. The only changes I put in are in caps below, along with a missing comma in the first select line
Select sum(d1), sum(d2), sum(d3), sum(d4)
from
(select
sum(a1) d1, sum(a2) d2, sum(a3) d3, sum(a4) d4
from a
union
SELECT
sum(b1) d1, sum(b2) d2, sum(b3) d3, sum(a4) d4
from B
union
select
sum(c1) d1, sum(c2) d2, sum(c3) d3, sum(c4) d4
FROM C
) AS ABC
October 23, 2007 at 9:27 pm
Sorry for the OBVIOUS mistakes.
It's been a rather unusual day today, but I'm glad yo got what you needed from it! :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply