Combining 2 table results 2

[999Dom999]

I have 2 tables with identical columns names. I have model numbers in each that I want return and if they are in both tables I want them to sum. I tried to use union but it just added the same model number twice. Any ideas how to achieve this below:


Table 1
---------------
Model quantity
BC123 20
BC124 10



Table 2
---------------
Model quantity
BC123 10
BC125 20


Desired result
--------------
BC123 30
BC124 10
BC125 20

Union gives me
--------------

BC123 20
BC123 10
BC124 10
BC125 20

I tried Union:

SELECT 
			Sa_product as model,
			Sa_desc as description,
			cname.cn_hclass as itemclass,
			cname.cn_hchar as charname,
			sum(Sa_qty) as quantity
		FROM SANAL 
			JOIN CNAME ON SANAL.Sa_product = CNAME.CN_REF
				WHERE 
					 cn_hclass ='BC' AND  
					 
					sa_trdate >= { d '2011-07-01'} AND 
					sa_trdate <= { d '2011-08-10'} AND 
					left(sa_product,3) != 'ZZZ' AND
					left(sa_product,3) != 'DIS' 
		UNION All
				SELECT 
			Sa_product as model,
			Sa_desc as description,
			cname.cn_hclass as itemclass,
			cname.cn_hchar as charname,
			sum(Sa_qty) as quantity
		FROM HSANAL 
			JOIN CNAME ON HSANAL.Sa_product = CNAME.CN_REF
				WHERE 
					 cn_hclass ='BC' AND  
					 
					sa_trdate >= { d '2011-07-01'} AND 
					sa_trdate <= { d '2011-08-10'} AND 
					left(sa_product,3) != 'ZZZ' AND
					left(sa_product,3) != 'DIS' 		
 
		 GROUP BY model
		 HAVING sum(Sa_qty) != 0 
		ORDER by quantity DESC

 

[IanWaterman]

How about

select Model, description, itemclass, charname
sum(quantity) as quantity
from (
SELECT Sa_product as model,            
Sa_desc as description,            
cname.cn_hclass as itemclass,            
cname.cn_hchar as charname,            
Sa_qty as quantity        
FROM SANAL             
JOIN CNAME ON SANAL.Sa_product = CNAME.CN_REF                
WHERE cn_hclass ='BC' 
AND sa_trdate >= { d '2011-07-01'} 
AND sa_trdate <= { d '2011-08-10'} 
AND left(sa_product,3) != 'ZZZ' 
AND left(sa_product,3) != 'DIS'         
UNION All                
SELECT Sa_product as model,            
Sa_desc as description,            
cname.cn_hclass as itemclass,            
cname.cn_hchar as charname,            
Sa_qty as quantity        
FROM HSANAL             
JOIN CNAME ON HSANAL.Sa_product = CNAME.CN_REF                
WHERE 
cn_hclass ='BC' 
AND sa_trdate >= { d '2011-07-01'} 
AND sa_trdate <= { d '2011-08-10'} 
AND left(sa_product,3) != 'ZZZ' 
AND left(sa_product,3) != 'DIS' 
)                  
GROUP BY model, description, itemclass, charname         
HAVING sum(quantity) != 0         
ORDER by quantity DESC

Ian

 

[999Dom999]

Thanks Ian, I'm sure that would work but I'm using odbc SQL via PHP to a Foxpro Database and unfortunately its a bit limited in functionality. If I try to do a select statement inside a select statement it throws a Syntax error, even this simple test didn't work - Select * from (SELECT * FROM CHCLAS)

Any other ideas how I might achieve this. I might have to do two queries then use php to do the merge but I was hoping to get all the work done in the query.

Thanks

Dom

 

[gmmastros]

even this simple test didn't work - Select * from (SELECT * FROM CHCLAS)

Your simple test has a syntax error. When you select from an embedded select like you are doing, it is called a derived table. You must supply an alias to the derived table and then use the alias outside of the parenthesis. Like this:

Select [!]AliasName.[/!]* from (SELECT * FROM CHCLAS) [!]AliasName[/!]

Foxpro Database

You posted your question in a Microsoft SQL Server forum. As such, there are many SQL Server guru's here. Some may also know FoxPro, but you may be better off posting your question in a FoxPro forum.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[999Dom999]

I know its foxpro but I'm using sql queries they are just a bit limited with functionality offered by odbc sql, I really need an odbc sql forum but this is the closest I found. I tried with the AliasName but it still threw a syntax error. Thanks

 

[Qik3Coder]

This may have a syntax error. Didn't have time to make all the tables.

SELECT c.CN_REF as model, COALESCE(s.Sa_desc, h.Sa_desc) as description, c.cn_hclass as itemclass, c.cn_hchar as charname, sum(IsNull(s.Sa_qty,0)+isNull(h.sa_qty,0)) as quantity
FROM CName c
LEFT JOIN SANAL s ON
	s.Sa_product = c.CN_REF
	AND s.sa_trdate >= { d '2011-07-01'} 
	AND s.sa_trdate <= { d '2011-08-10'} 
	AND left(s.sa_product,3) != 'ZZZ' 
	AND left(s.sa_product,3) != 'DIS' 
LEFT JOIN HSANAL h ON
	h.Sa_product = c.CN_REF
	AND h.sa_trdate >= { d '2011-07-01'} 
	AND h.sa_trdate <= { d '2011-08-10'} 
	AND left(h.sa_product,3) != 'ZZZ' 
	AND left(h.sa_product,3) != 'DIS' 
WHERE 
	c.cn_hclass ='BC'
 GROUP BY model, COALESCE(s.Sa_desc, h.Sa_desc), c.cn_hclass,  c.cn_hchar
 HAVING sum(IsNull(s.Sa_qty,0)+isNull(h.sa_qty,0)) > 0 
 ORDER by quantity DESC

Lodlaiden

I finished a 2d game engine tutorial.
My wife's first comment: Why doesn't the waterfall move?

 

[markros]

In VFP9 (the latest and last version of FoxPro) you can use derived tables in SQL. But, as George pointed out, you need to give derived table an alias.

PluralSight Learning Library

 

[999Dom999]

It is VFP9 but I was using ODBC to connect and ODBC-SQL is limited, I found out I can use OLE DB to connect instead this supports derived tables.

I used Ian's example it needed a comma after charname and an Alias name after the close bracket and works perfectly!

Thanks so much