1 Replies - 1002 Views - Last Post: 19 November 2009 - 03:41 AM Rate Topic: -----

#1 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Combining sums from two sql statements

Post icon  Posted 19 November 2009 - 02:15 AM

I'm trying to combine two SQL statements to get a single totalled result.

I have two kinds of orders, each stored in a different table. Call the tables "ordersa" and "ordersb". (Table names and product names are being changed here to make things a little simpler.)

A third table holds the product descriptions used by both of the first. Call it "products".

In what seems to be a plot by the cosmos to make things more complicated for me, the status of each order in "ordersa" is stored in another table - call it "statusa". (Statuses for ordersb are stored in ordersb.)

I want to find out how many of each product has sold, for both order types for orders in a particular status.

Here is how I can get the totals for ordersa:

select sum(a.amount) as quantity, c.product from ordersa as a, statusa as b, products as c where a.productcode=c.productcode and a.orderid=b.orderid and b.status = "C" group by c.productcode


The result might look like this:

quantity  product			  
			 3  Widget 
			 4  Wottle  
			 3  Thingamajig	
			 5  Doohickey	   
			 7  Franjamerka 
			 1  Flimflam  
			 3  Gorch  
			 9  Whatsit



Getting the totals for ordersb looks like this:

select sum(a.quantity) as quantity, c.product from ordersb as a, products as c where a.productcode=c.productcode and a.status = "C"  group by c.productcode


Which produces results like this:
quantity  product					   
			 1  Bloopsey 
			 3  Widget		 
		   24  Rangatang 
		   33  Wottle		  
		   24  Thingamaji		   
		   24  Doohickey			   
		   24  Farkle				   
		   26  Spleenbender		 
		   24  Pan Galactic Gargle Blaster

Notice that each orders table references products the other doesn't, and some products are referenced in both order tables. Also notice that ordersa calls quantity "amount" whereas ordersb calls it quantity. (Just pointing that out in case anybody was wondering why they were different in the two statemetns.) Each record in each of the orders tables is a single line item of an order and both tables contain: orderid, productcode, amount/quantity among other fields not relevant here. The table statusa contains orderid and status, and products contains productcode and product.


What I need is a combined result:

quantity  product			  
			 6  Widget 
		   37  Wottle  
		   27  Thingamajig	
		   29  Doohickey	   
			 7  Franjamerka 
			 1  Flimflam  
			 3  Gorch  
			 9  Whatsit
			 1  Bloopsey 
		   24  Rangatang 
		   24  Farkle				   
		   26  Spleenbender		 
		   24  Pan Galactic Gargle Blaster

Some things that didn't work:

select sum(a.amount + b.quantity) as quantity, c.product  
from ordersa as a, ordersb as b, products as c, statusa as d 
where a.orderid=d.orderid and a.productcode=c.productcode 
	and b.productcode=c.productcode and d.status = "C" 
	and b.status = "C" and group by c.product

select sum(a.amount + b.quantity) as quantity, c.product
from ordersa as a, ordersb as b, products as c, statusa as d 
where a.orderid = d.orderid 
	and (a.productcode=c.productcode OR b.productcode=c.productcode) 
	and d.status = "C" and b.status = "C";

(select sum(a.quantity) as quantity, c.product 
  from ordersb as a, products as c 
  where a.productcode=c.productcode 
  and a.status="C" 
  group by c.productcode) 
union 
(select sum(a.amount) as quantity, c.product 
  from ordersa as a, statusa as b, products as c 
  where a.productcode=c.productcode 
  and a.orderid=b.orderid and b.status = "C" 
  group by c.product) 
order by c.product;


... And several others that I'm sure are even further from being correct.

I'm pretty sure I need to use an outer join to make this happen, but outer joins have yet to set themselves into my brain in any cohesive manner. I'm just not sure how to go about this.

Any input would be greatly valued.

Thanks,
m

Is This A Good Question/Topic? 0
  • +

Replies To: Combining sums from two sql statements

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5749
  • View blog
  • Posts: 12,556
  • Joined: 16-October 07

Re: Combining sums from two sql statements

Posted 19 November 2009 - 03:41 AM

That last one should have worked, acutally. However, the double quotes on "C" will break most SQL. The group by c.productcode and result with c.product also shouldn't work.

First thing I like to do is lay things out so I can see them:
select sum(a.amount) as quantity, c.product 
	from ordersa as a, statusa as b, products as c
	where a.productcode=c.productcode 
		and a.orderid=b.orderid 
		and b.status = 'C'
	group by c.productcode

select sum(a.quantity) as quantity, c.product 
	from ordersb as a, products as c 
	where a.productcode=c.productcode 
		and a.status = 'C'
	group by c.productcode



I'm going to move these into ansi standard syntax:
select c.product, sum(a.amount) as quantity
	from products as c
		inner join ordersa as a on a.productcode=c.productcode 
		inner join statusa as b on a.orderid=b.orderid and b.status = 'C'
	group by c.productcode

select c.product, sum(a.amount) as quantity
	from products as c
		inner join ordersb as a on a.productcode=c.productcode and a.status='C'
	group by c.productcode



We can remove products for a moment:
select a.productcode, sum(a.amount) as quantity
	from ordersa as a 
		inner join statusa as b 
			on a.orderid=b.orderid and b.status = 'C'
	group by a.productcode

select a.productcode, sum(a.amount) as quantity
	from ordersb as a 
	where a.status='C'
	group by a.productcode



I like that. Let's wrap it up with a sub query and a union:
select c.product, sum(ab.quantity) as quantity
	from products as c
		inner join (
			select a.productcode, sum(a.amount) as quantity
				from ordersa as a 
					inner join statusa as b 
						on a.orderid=b.orderid and b.status = 'C'
				group by a.productcode
			union
			select a.productcode, sum(a.amount) as quantity
				from ordersb as a 
				where a.status='C'
				group by a.productcode
		) ab on on ab.productcode=c.productcode 
	group by c.product



If that inner union gives you trouble, you can simplify things and do a late group by:
select c.product, sum(ab.amount) as quantity
	from products as c
		inner join (
			select a.productcode, a.amount
				from ordersa as a 
					inner join statusa as b 
						on a.orderid=b.orderid and b.status='C'
			union
			select a.productcode, a.amount
				from ordersb as a 
				where a.status='C'
		) ab on on ab.productcode=c.productcode 
	group by c.product


This post has been edited by baavgai: 19 November 2009 - 03:41 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1