Обсуждение: Substract queries

Hi all,<br /><br />I was wondering how can I substract result between select queries. I mean I want to issue a query
thatdoes this :<br />(select * from mytable where condition1) - (select * from mytable where condition2)<br /><br />
Thanksto all<br /><br />Nacef<br /> 

Nacef LABIDI wrote:
> Hi all,
> 
> I was wondering how can I substract result between select queries. I mean I
> want to issue a query that does this :
> (select * from mytable where condition1) - (select * from mytable where
> condition2)

If the subqueries return single (scalar) results, you can just subtract 
them directly:

SELECT (SELECT COUNT(id) FROM table1) - (SELECT COUNT(id) FROM table2)


However, I'm guessing you REALLY want to match the records up in two 
tables and compare them.

In that case what you need to do is read this:

http://www.postgresql.org/docs/8.3/static/tutorial-join.html

and this:

http://www.postgresql.org/docs/8.3/static/queries.html

including this:

http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html#QUERIES-FROM

then use a JOIN to combine both tables, matching up corresponding 
records in each by (eg) an id field, then subtracting the fields.

Say I have

tablea
----------
ida   numa
----------
1       11
2       48
3       82
5       14


tableb
----------
idb   numb
5     20
2     30
3     40
1     50


then if I execute:

SELECT ida, numa, numb, numa - numb AS sub
FROM tablea, tableb
WHERE tablea.ida = tableb.idb';

I'll get a result like:

ida     numa    numb    sub
---------------------------
2       48      30      18
5       14      20      -6
3       82      40      42
1       11      50      -39

which is what I suspect you want. Note that the results do not appear in 
any particular order.



If what you really want is a query that returns all records in the first 
query EXCEPT those returned by the second query, then see:

http://www.postgresql.org/docs/8.3/static/queries-union.html

--
Craig Ringer

On 22 maj 2008, at 17.15, Nacef LABIDI wrote:
> I was wondering how can I substract result between select queries.  
> I mean I want to issue a query that does this :
> (select * from mytable where condition1) - (select * from mytable  
> where condition2)

If you (as implied above) query the same table in both cases, just do:

SELECT * FROM mytable WHERE condition1 AND NOT condition2

Otherwise, use EXCEPT:

SELECT * FROM mytable1 WHERE condition1
EXCEPT
SELECT * FROM mytable2 WHERE condition2

in which case both queries must return the same type of rows.




Sincerely,

Niklas Johansson

Hi ,The query is like this ,
Except
SELECT * from ((SELECT COUNT(id) FROM table1) Except (SELECT COUNT(id)
FROM table2))tmp
Regards,
Ram

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer
Sent: Thursday, May 22, 2008 9:05 PM
To: Nacef LABIDI
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Substract queries

Nacef LABIDI wrote:
> Hi all,
>
> I was wondering how can I substract result between select queries. I
mean I
> want to issue a query that does this :
> (select * from mytable where condition1) - (select * from mytable
where
> condition2)

If the subqueries return single (scalar) results, you can just subtract
them directly:

SELECT (SELECT COUNT(id) FROM table1) - (SELECT COUNT(id) FROM table2)


However, I'm guessing you REALLY want to match the records up in two
tables and compare them.

In that case what you need to do is read this:

http://www.postgresql.org/docs/8.3/static/tutorial-join.html

and this:

http://www.postgresql.org/docs/8.3/static/queries.html

including this:

http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html
#QUERIES-FROM

then use a JOIN to combine both tables, matching up corresponding
records in each by (eg) an id field, then subtracting the fields.

Say I have

tablea
----------
ida   numa
----------
1       11
2       48
3       82
5       14


tableb
----------
idb   numb
5     20
2     30
3     40
1     50


then if I execute:

SELECT ida, numa, numb, numa - numb AS sub
FROM tablea, tableb
WHERE tablea.ida = tableb.idb';

I'll get a result like:

ida     numa    numb    sub
---------------------------
2       48      30      18
5       14      20      -6
3       82      40      42
1       11      50      -39

which is what I suspect you want. Note that the results do not appear in

any particular order.



If what you really want is a query that returns all records in the first

query EXCEPT those returned by the second query, then see:

http://www.postgresql.org/docs/8.3/static/queries-union.html

--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql