Combine two Select statements - 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

General SQL Server Forums

New to SQL Server Programming

Combine two Select statements

Author

Topic

wschrock
Starting Member

2 Posts

Posted - 2011-03-07 : 14:17:32

Please help me combine these two select statement to return JOBNO, TotalSales + HeaderSales, Entered. I am very new to SQL and was able to create the seperate statements but having much problems with final desired results. Thanks in advance.

SELECT "job_hdr"."order-no" AS JOBNO, "job_hdr"."enter-date" AS ENTERED, SUM(("job_hdr"."QtyOrdered" * "job_hdr"."unit-price") - ("job_hdr"."QtyOrdered" * "job_hdr"."disc-amt")) AS HeaderSales
FROM "E940LIVE"."PUB"."job-hdr" "job_hdr"
WHERE "job_hdr"."company"='01'
AND NOT ("job_hdr"."order-no" LIKE '70000%')
GROUP BY JOBNO,ENTERED
HAVING ENTERED >= {?A_Start_Date} AND ENTERED <= {?B_End_Date}
ORDER BY JOBNO

SELECT "job_line"."order-no" AS JOBNO, "job_line"."chg-dt" AS ENTERED, SUM(("job_line"."QtyOrdered" * "job_line"."unit-price") - ("job_line"."QtyOrdered" * "job_line"."disc-amt")) AS TotalSales
FROM "E940LIVE"."PUB"."job-line" "job_line"
WHERE "job_line"."company"='01'
AND NOT ("job_line"."order-no" LIKE '70000%')
GROUP BY JOBNO,ENTERED
HAVING ENTERED >= {?A_Start_Date} AND ENTERED <= {?B_End_Date}
ORDER BY JOBNO

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-07 : 14:56:48

check this

Select A.JOBNO,A.HeaderSales+B.TotalSales,A.Entered

From

(

SELECT "job_hdr"."order-no" AS JOBNO, "job_hdr"."enter-date" AS ENTERED, SUM(("job_hdr"."QtyOrdered" * "job_hdr"."unit-price") - ("job_hdr"."QtyOrdered" * "job_hdr"."disc-amt")) AS HeaderSales
FROM "E940LIVE"."PUB"."job-hdr" "job_hdr"
WHERE "job_hdr"."company"='01'
AND NOT ("job_hdr"."order-no" LIKE '70000%')
GROUP BY JOBNO,ENTERED
HAVING ENTERED >= {?A_Start_Date} AND ENTERED <= {?B_End_Date}
) A

Inner Join

(

SELECT "job_line"."order-no" AS JOBNO, "job_line"."chg-dt" AS ENTERED, SUM(("job_line"."QtyOrdered" * "job_line"."unit-price") - ("job_line"."QtyOrdered" * "job_line"."disc-amt")) AS TotalSales
FROM "E940LIVE"."PUB"."job-line" "job_line"
WHERE "job_line"."company"='01'
AND NOT ("job_line"."order-no" LIKE '70000%')
GROUP BY JOBNO,ENTERED
HAVING ENTERED >= {?A_Start_Date} AND ENTERED <= {?B_End_Date}

) B on A.JOBNO=B.JOBNO and A.Entered=B.Entered

Order by JobNo

Cheers
MIK

wschrock
Starting Member

2 Posts

Posted - 2011-03-07 : 16:24:07

Thanks, MIK

Worked great except I had to remove "Order by JobNO" I was getting error "Column reference JOBNO is ambiguous. (13852)". Don't know why removing it worked I just tried different things until it worked.

Wayne

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-10 : 10:33:38

JobNo is available in both the queries so you need to use alias name

Order by A.JobNo

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources