subtracting 2 columns, with the group by clause

rocker86 · #1 (**permalink**) 06-23-11, 06:31

Hi,
I have two select statements

Stmt1:

Code:

Select count(*) count, fruit from table1 group by fruit

Result:

count-----fruit

12--------Apple
34--------Orange
24--------Melon

Stmt2:

Code:

Select count(*) count, fruit from table2 group by fruit

Result:

count-----fruit

10--------Apple
30--------Orange
20--------Melon

Can some one please help me with a select statement which gives me the difference between the above two results, with the group by clause intact

Result::

Code:

count-----fruit

2--------Apple
4--------Orange
4--------Melon

thanks in advace!

stolze · #2 (**permalink**) 06-23-11, 07:08

Code:

SELECT t1.count - t2.count, t1.fruit
FROM ( <query1> ) AS t1 JOIN ( <query2> ) AS t2 ON ( t1.fruit = t2.fruit )

Just remember: pretty much everything in SQL is a table. A query produces a table as output, and you can use a table in the FROM clause of a query, for example. Just combine these two things and you have subselects.

Internet Services

Web Development

Digital Marketing

Consumer Tech