Union Select Question 1

[buddafish]

All,
I am attempting to join 2 tables containing customer number and sales. Table one has current month. Table two is all other months. Anyway, this has always worked with Access, but I can't seem to get SQL to return 1 record for each customer. Any help is appreciated.

SELECT STC_CUS_NO1 AS CUSTOMER, SUM(STC_TOT_INTL_AMT)AS SALES
FROM root_SAHSTC
WHERE STC_ACCTNG_PER BETWEEN 200501 AND 200512
GROUP BY STC_CUS_NO1
UNION
SELECT SAT_CUS_NO1 AS CUSTOMER, SUM(SAT_TOT_INTL_AMT) AS SALES
FROM root_SAHSAT
WHERE SAT_ACCTNG_PER > 200412
GROUP BY SAT_CUS_NO1
ORDER BY STC_CUS_NO1


Thanks
Scottie

 

[Pattycake245]

Perhaps this:

SELECT CUSTOMER, SUM(SALES)
FROM(
SELECT STC_CUS_NO1 AS CUSTOMER, SUM(STC_TOT_INTL_AMT)AS SALES
FROM root_SAHSTC
WHERE STC_ACCTNG_PER BETWEEN 200501 AND 200512
GROUP BY STC_CUS_NO1
UNION
SELECT SAT_CUS_NO1 AS CUSTOMER, SUM(SAT_TOT_INTL_AMT) AS SALES
FROM root_SAHSAT
WHERE SAT_ACCTNG_PER > 200412
GROUP BY SAT_CUS_NO1) AS RESULTS
GROUP BY SAT_CUS_NO1
ORDER BY SAT_CUS_NO1

If a customer has sales from both tables, they won't merge into one, I believe, because the sales amounts are probably different, hence it will produce two entries. I assume that is what your problem is.

Tim

 

[r937]

use UNION ALL instead of UNION



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[buddafish]

Thanks Tim,

Right on target!

Scottie