SQL Syntax Update with Union

What is the correct syntax to enable this script to group by the projnumber and perform the desired up? I am having trouble positioning the GROUP BY clause. There is a UNION between two tables in between. The first table contain billing information in one table and the second table has billing information in a second table.
UPDATE [MDGPWORK].[dbo].[JOBSTATUS]
SET BILLINGACT = pab.billamnt
FROM [MDGPWORK].[dbo].[JOBSTATUS] INNER JOIN 
(
    SELECT pbo.paprojnumber as jobnumber,sum(PABILLINGAMOUNT) as billamnt
	FROM PA23200 pbo
	INNER JOIN (
		select rtrim(pacontnumber) as pacontnumber
		from PA01101) as proj
	ON pbo.paprojnumber = proj.pacontnumber
	UNION ALL
	SELECT pbh.paprojnumber as jobnumber,PABILLINGAMOUNT as billamnt
	FROM PA33200 pbh
	INNER JOIN (
		select rtrim(pacontnumber) as pacontnumber
		from PA01101) as proj
	ON pbh.paprojnumber = proj.pacontnumber
	GROUP BY x.paprojnumber
) pab
ON [MDGPWORK].[dbo].[JOBSTATUS].JOBNUMBER = pab.paprojnumber

Open in new window

LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
Try this query. Your query is not properly grouped and something is missing.
UPDATE  [MDGPWORK].[dbo].[JOBSTATUS]
SET     BILLINGACT = pab.billamnt
FROM    [MDGPWORK].[dbo].[JOBSTATUS]
        INNER JOIN ( SELECT jobnumber ,
                            SUM(billamnt) billamnt
                     FROM   ( SELECT    pbo.paprojnumber AS jobnumber ,
                                        SUM(PABILLINGAMOUNT) AS billamnt
                              FROM      PA23200 pbo
                                        INNER JOIN ( SELECT RTRIM(pacontnumber) AS pacontnumber
                                                     FROM   PA01101
                                                   ) AS proj ON pbo.paprojnumber = proj.pacontnumber
                              GROUP BY  pbo.paprojnumber
                              UNION ALL
                              SELECT    pbh.paprojnumber AS jobnumber ,
                                        SUM(PABILLINGAMOUNT) AS billamnt
                              FROM      PA33200 pbh
                                        INNER JOIN ( SELECT RTRIM(pacontnumber) AS pacontnumber
                                                     FROM   PA01101
                                                   ) AS proj ON pbh.paprojnumber = proj.pacontnumber
                              GROUP BY  pbh.paprojnumber
                            ) X
                     GROUP BY x.jobnumber
                   ) pab ON [MDGPWORK].[dbo].[JOBSTATUS].JOBNUMBER = pab.jobnumber

Open in new window


Raj
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ephraim WangoyaCommented:
try
UPDATE [MDGPWORK].[dbo].[JOBSTATUS]
SET BILLINGACT = pab.billamnt
FROM [MDGPWORK].[dbo].[JOBSTATUS] INNER JOIN 
(
    SELECT * 
    FROM
		(SELECT pbo.paprojnumber as jobnumber,sum(PABILLINGAMOUNT) as billamnt
		FROM PA23200 pbo
		INNER JOIN (
			select rtrim(pacontnumber) as pacontnumber
			from PA01101) as proj
		ON pbo.paprojnumber = proj.pacontnumber
		UNION ALL
		SELECT pbh.paprojnumber as jobnumber,PABILLINGAMOUNT as billamnt
		FROM PA33200 pbh
		INNER JOIN (
			select rtrim(pacontnumber) as pacontnumber
			from PA01101) as proj
		ON pbh.paprojnumber = proj.pacontnumber
	
      ) A GROUP BY paprojnumber
) pab
ON [MDGPWORK].[dbo].[JOBSTATUS].JOBNUMBER = pab.paprojnumber

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Something like this (using a derived table) should work:
UPDATE tto
SET BILLINGACT = pab.billamnt
FROM [MDGPWORK].[dbo].[JOBSTATUS] tto
INNER JOIN (
    SELECT paprojnumber, SUM(billamnt) AS billamnt
	FROM (
       SELECT pbo.paprojnumber as jobnumber, PABILLINGAMOUNT as billamnt
	   FROM PA23200 pbo
	   INNER JOIN (
	      select rtrim(pacontnumber) as pacontnumber
		  from PA01101
	   ) as proj ON pbo.paprojnumber = proj.pacontnumber
	   UNION ALL
	   SELECT pbh.paprojnumber as jobnumber, PABILLINGAMOUNT as billamnt
	   FROM PA33200 pbh
	   INNER JOIN (
	      select rtrim(pacontnumber) as pacontnumber
		  from PA01101
	   ) as proj ON pbh.paprojnumber = proj.pacontnumber
	) derived
	GROUP BY paprojnumber
) pab ON tto.JOBNUMBER = pab.paprojnumber

Open in new window

0
Rajkumar GsSoftware EngineerCommented:
This also should work
UPDATE  [MDGPWORK].[dbo].[JOBSTATUS]
SET     BILLINGACT = pab.billamnt
FROM    [MDGPWORK].[dbo].[JOBSTATUS]
        INNER JOIN ( SELECT jobnumber ,
                            SUM(billamnt)
                     FROM   ( SELECT    pbo.paprojnumber AS jobnumber ,
                                        PABILLINGAMOUNT AS billamnt
                              FROM      PA23200 pbo
                                        INNER JOIN ( SELECT RTRIM(pacontnumber) AS pacontnumber
                                                     FROM   PA01101
                                                   ) AS proj ON pbo.paprojnumber = proj.pacontnumber
                              UNION ALL
                              SELECT    pbh.paprojnumber AS jobnumber ,
                                        PABILLINGAMOUNT AS billamnt
                              FROM      PA33200 pbh
                                        INNER JOIN ( SELECT RTRIM(pacontnumber) AS pacontnumber
                                                     FROM   PA01101
                                                   ) AS proj ON pbh.paprojnumber = proj.pacontnumber
                            ) x
                     GROUP BY x.jobnumber
                   ) pab ON [MDGPWORK].[dbo].[JOBSTATUS].JOBNUMBER = pab.jobnumber

Open in new window

0
rwheeler23Author Commented:
The first solution is the only one that runs and works. Thanks to all who responded. I always learn something new from the answers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.