[hamidkhl]

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!

[Atli]

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.

[baavgai]

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.

[hamidkhl]

thank you guys