3 Replies - 758 Views - Last Post: 09 May 2012 - 11:23 AM Rate Topic: -----

#1 hamidkhl  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 03-November 08

How can "-" 2 queries

Posted 09 May 2012 - 09:02 AM

Hi

I wrote 2 gueries, both of them return set of integer values how can I Subtract them?

first query:

SELECT Goods.Title, SUM([Transaction].Price)
FROM [Transaction] INNER JOIN Market ON
						[Transaction].Market_Id=Market.Id
				   INNER JOIN MarketType ON
						Market.MarketType_Id=MarketType.Id 
				   INNER JOIN Goods ON
						[Transaction].Goods_Id=Goods.Id
WHERE (MarketType_Id=1)
GROUP BY Goods.Title





SELECT Goods.Title, SUM([Transaction].Price)
FROM [Transaction] INNER JOIN Market ON
						[Transaction].Market_Id=Market.Id
				   INNER JOIN MarketType ON
						Market.MarketType_Id=MarketType.Id 
				   INNER JOIN Goods ON
						[Transaction].Goods_Id=Goods.Id
WHERE (MarketType_Id=2)
GROUP BY Goods.Title




in fact I want to do this but this error happens: Incorrect syntax near '-'.

(SELECT Goods.Title, SUM([Transaction].Price)
FROM [Transaction] INNER JOIN Market ON
						[Transaction].Market_Id=Market.Id
				   INNER JOIN MarketType ON
						Market.MarketType_Id=MarketType.Id 
				   INNER JOIN Goods ON
						[Transaction].Goods_Id=Goods.Id
WHERE (MarketType_Id=1)
GROUP BY Goods.Title) - (SELECT Goods.Title, SUM([Transaction].Price)
FROM [Transaction] INNER JOIN Market ON
						[Transaction].Market_Id=Market.Id
				   INNER JOIN MarketType ON
						Market.MarketType_Id=MarketType.Id 
				   INNER JOIN Goods ON
						[Transaction].Goods_Id=Goods.Id
WHERE (MarketType_Id=2)
GROUP BY Goods.Title)



pleas help me!

Is This A Good Question/Topic? 0
  • +

Replies To: How can "-" 2 queries

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3635
  • View blog
  • Posts: 5,756
  • Joined: 08-June 10

Re: How can "-" 2 queries

Posted 09 May 2012 - 09:47 AM

Just to clarify, you want something like this to happen?

First query:
+--------+-------+
| title  | sum() |
+--------+-------+
| First  |   100 |
| Second |   250 |
+--------+-------+

Second query:
+--------+-------+
| title  | sum() |
+--------+-------+
| First  |    25 |
| Second |   185 |
+--------+-------+

Expected results:
+--------+-------+
| title  | sum() |
+--------+-------+
| First  |    75 |
| Second |    65 |
+--------+-------+



If so, I would imagine you could do something along these lines:
SELECT
	f.title, 
	f.price_sum AS fist_sum, 
	s.price_sum AS second_sum, 
	(f.sum - s.sum) AS total
FROM (
	SELECT title, SUM(price) AS price_sum
	FROM theTable
	WHERE some_id = 1
	GROUP BY title
) AS f
INNER JOIN (
	SELECT title, SUM(price) AS price_sum
	FROM theTable
	WHERE some_id = 2
	GROUP BY title
) AS s
	ON f.title = s.title;


The idea here is to join the result sets from both queries so that both sums exist in the same row in the final result set. Then you can simply subtract them and return the result in a new column.
Was This Post Helpful? 1
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5641
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: How can "-" 2 queries

Posted 09 May 2012 - 11:02 AM

It doesn't look like you need the MarketType table in those queries.

Consider:
SELECT c.Title, 
		SUM(case when b.MarketType_Id=1 then a.Price else 0 end) as Price1,
		SUM(case when b.MarketType_Id=2 then a.Price else 0 end) as Price2
	FROM [Transaction] a
		INNER JOIN Market b
			ON a.Market_Id = b.Id
		INNER JOIN Goods c
			ON a.Goods_Id=c.Id
	GROUP BY c.Title



Now, just:
SELECT c.Title, 
		SUM(case when b.MarketType_Id=1 then a.Price else 0 end)
		- SUM(case when b.MarketType_Id=2 then a.Price else 0 end) as Price
	FROM [Transaction] a
		INNER JOIN Market b
			ON a.Market_Id = b.Id
		INNER JOIN Goods c
			ON a.Goods_Id=c.Id
	GROUP BY c.Title



I least, I think that's what you're after.
Was This Post Helpful? 2
  • +
  • -

#4 hamidkhl  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 03-November 08

Re: How can "-" 2 queries

Posted 09 May 2012 - 11:23 AM

thank you guys :bigsmile:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1