• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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

0
rwheeler23
Asked:
rwheeler23
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now