Re: Combining result sets

On Wed, 26 Apr 2006 12:28:02 -0700, dataGirl wrote:

Hi there,

In an effort to give you a clear idea of what I'm doing, I'm going to give
you as much info as possible. Hopefully, this isn't overkill...

Hi dataGirl,

In fact, you gave not enough information... :-)

The best you can do when yoou aks for help in these groups is to include
the CREATE TABLE statements for all tables involved (including all
constraints and properties, though irrelevant columns may be omitted),
some INSERT statements with sample data to illustrate the problem and
the expected results. See www.aspfaq.com/5006.

(snip)
I then used a "Union all" statement to combine the two lists which yielded:

Code Description Units
1111 ProductY 2
2222 ProductX 6
2222 ProductX 12
3333 ProductZ 1


Question: Seeing as ProductX is common in both result sets. Is there any
way I can list the same products as one row? For example, I would like for
there to only be one row for ProductX, which appears as " 2222 ProductX
18".

One possible way is to use UNION ALL (not UNION - less efficient and it
might return incorrect results in your case) inside a derived table and
add a GROUP BY:

SELECT Code, Description, SUM(Units)
FROM (SELECT Code, Description, ??? AS Units
FROM ...
WHERE ...
UNION ALL
SELECT Code, Description, ??? AS Units
FROM ...
WHERE ...) AS Der
GROUP BY Code, Description


--
Hugo Kornelis, SQL Server MVP
.