query sum union problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query sum union problem

Author

Topic

pelolori
Starting Member

15 Posts

Posted - 2010-01-02 : 06:46:41

Hi,
i've a problem with this query. i want to sum the rsult of two union query, but sql server give me a sintax error. Can anyone help me. thanks a lot

this is the query

Select sum ('amt') from
(select t0.project as ' pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as ' pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by
t2.project
)

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-02 : 07:00:22

You will need to alias the the "from" clause . i.e

Select sum ('amt') from
(select t0.project as ' pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as ' pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by
t2.project
) myalias1

and reference accordingly in the SELECT part

Jack Vamvas
--------------------
http://www.ITjobfeed.com (IT jobs)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-02 : 07:07:43

Select sum (amt)as amt from
(select t0.project as ' pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as ' pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by
t2.project
)

pelolori
Starting Member

15 Posts

Posted - 2010-01-02 : 07:17:58

Hi jack,
thanks a lot the syntax can be this:

Select sum ('BIGTOTAL') from
(select t0.project as ' pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as ' pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by
t2.project
) BIGTOTAL

i try tu run it bat hte system give me an error to Varchar: In fact the field t0.project and t2.project is a varchar while only the field 'amt' is a numeric field

pelolori
Starting Member

15 Posts

Posted - 2010-01-02 : 07:23:13

hi bklr,
i'll try tu use your code but the system give me the same syntax error:

i'll try tu use it:
Select sum ('amt') as amt from
(select t0.project as ' pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as ' pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by
t2.project
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 07:57:31

quote:
Originally posted by pelolori

hi bklr,
i'll try tu use your code but the system give me the same syntax error:

i'll try tu use it:
Select sum ('amt') as amt from
(select t0.project as ' pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as ' pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by
t2.project
)

you dont need '' around fieldname inside sum. it should be
sum (amt) not
sum ('amt')

pelolori
Starting Member

15 Posts

Posted - 2010-01-02 : 08:20:59

ok, now the query run but i don't understand why not the system doesn't calculate the subtotal for a t0.projcet and t2.project
i run this query:
Select sum (amt) as amt from
(select t0.project as 'pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as 'pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by t2.project
) amt

the system give me this result:
project result
null 25000
this 25000 is not correct, i want to sum for only project code that i insert into my two select query. How I can i do it?
for example
project total sum
1 12000 = 7000 for the first query and 5000 for the second query
2 13000 = 6000 for the first query and 7000 for the second query

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 08:34:16

do you mean?


Select pro,sum (amt) as amt from
(select t0.project as 'pro', sum(T1.TotalSumSy) as 'amt' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry where t0.project != '' group by t0.project
union all
Select t2.project as 'pro', sum(T3.TotalSumSy) as 'amt' FROM OPCH T2 Inner join PCH1 T3 on t2.docentry = t3.docentry where t2.project != ' ' group by t2.project
) amt 
group by pro

pelolori
Starting Member

15 Posts

Posted - 2010-01-02 : 08:41:48

thanks a lot
my error is to insert '' to field pro.
i resolve my problem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 08:43:58

why do you insert '' to pro?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources