Thread: 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 />
<span style="font-family: verdana,sans-serif;">Probably you are looking for EXCEPT.</span><br style="font-family: verdana,sans-serif;"/><br style="font-family: verdana,sans-serif;" /><span style="font-family: verdana,sans-serif;">SELECT* FROM Tbl1 WHERE a=1</span><br style="font-family: verdana,sans-serif;" /><span style="font-family:verdana,sans-serif;">EXCEPT </span><br style="font-family: verdana,sans-serif;" /><span style="font-family:verdana,sans-serif;">SELECT * FROM tbl2 WHERE a=1 and b=1</span>;<br style="font-family: verdana,sans-serif;"/><br style="font-family: verdana,sans-serif;" /><span style="font-family: verdana,sans-serif;"><a href="http://www.postgresql.org/docs/8.3/interactive/sql-select.html">http://www.postgresql.org/docs/8.3/interactive/sql-select.html</a></span><br style="font-family:verdana,sans-serif;" /><br style="font-family: verdana,sans-serif;" /><span style="font-family: verdana,sans-serif;">Regards,</span><brstyle="font-family: verdana,sans-serif;" /><b style="font-family: verdana,sans-serif;">RobinsTharakan</b><br /><br /><div class="gmail_quote">---------- Forwarded message ----------<br />From:<b class="gmail_sendername">Nacef LABIDI</b> <<a href="mailto:nacef.l@gmail.com">nacef.l@gmail.com</a>><br />Date:Thu, May 22, 2008 at 8:45 PM<br /> Subject: [SQL] Substract queries<br />To: <a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a><br/><br /><br />Hi all,<br /><br />I was wondering howcan I substract result between select queries. I mean I want to issue a query that does this :<br /> (select * from mytablewhere condition1) - (select * from mytable where condition2)<br /><br /> Thanks to all<br /><font color="#888888"><br/>Nacef<br /></font></div><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
Thanks to all the EXEPT keyword is what I was looking for<br /><br /><div class="gmail_quote">On Thu, May 22, 2008 at 5:36PM, Niklas Johansson <<a href="mailto:spot@tele2.se">spot@tele2.se</a>> wrote:<br /><blockquote class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><divclass="Ih2E3d"><br /> On 22 maj 2008, at 17.15, Nacef LABIDI wrote:<br /><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> I was wondering how canI substract result between select queries. I mean I want to issue a query that does this :<br /> (select * from mytablewhere condition1) - (select * from mytable where condition2)<br /></blockquote><br /></div> If you (as implied above)query the same table in both cases, just do:<br /><br /> SELECT * FROM mytable WHERE condition1 AND NOT condition2<br/><br /> Otherwise, use EXCEPT:<br /><br /> SELECT * FROM mytable1 WHERE condition1<br /> EXCEPT<br /> SELECT* FROM mytable2 WHERE condition2<br /><br /> in which case both queries must return the same type of rows.<br /><br/><br /><br /><br /> Sincerely,<br /><font color="#888888"><br /> Niklas Johansson<br /><br /><br /><br /></font></blockquote></div><br/>
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