Thread: Sv: how to build this query ??? Please help !!!
Something like this oughtta work (not tested) SELECT a_nr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zdr') AS #zdr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcu') AS #zcu, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcr') AS #zcr, product, state FROM orders /Nikolaj -----Oprindelig meddelelse----- Fra: juerg.rietmann@pup.ch <juerg.rietmann@pup.ch> Til: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org> Dato: 3. januar 2001 16:16 Emne: [SQL] how to build this query ??? Please help !!! >Hello there > >I have a problem, building a query. Who can help ? >I use postgreSQL 7.0.3. > >Thanks for any help and suggestions ... jr > >orders >a_nr ¦ product ¦ state >----------------------------- >11 ¦ tp ¦ aaaa >12 ¦ fi ¦ bbbb >13 ¦ tp ¦ cccc >14 ¦ ok ¦ eeee >15 ¦ dm ¦ aaaa > >cylinders >z_a_nr ¦ z_status >------------------------------ >11 ¦ zdr >11 ¦ zdr >12 ¦ zdr >12 ¦ zcu >13 ¦ zdr >13 ¦ zcu >13 ¦ zcu >13 ¦ zcr >15 ¦ zcu >15 ¦ zcu >15 ¦ zdr > >I need a query for the following output : > >a_nr ¦ #cylinder #zdr #zcu #zcr product state >---------------------------------------------------------------------- >11 2 2 0 0 dm aaaa >12 2 1 1 0 tp aaaa >13 4 1 2 1 fi bbbb >14 0 0 0 0 ok eeee >15 3 1 2 0 dm aaaa > > > > > >============================================ >PFISTER + PARTNER, SYSTEM - ENGINEERING AG >Juerg Rietmann >Grundstrasse 22a >6343 Rotkreuz >Switzerland > >phone: +4141 790 4040 >fax: +4141 790 2545 >mobile: +4179 211 0315 >============================================ > >
> [...] > SELECT a_nr, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, > [...] > FROM orders > [...] Is this a documented feature? Where can I find more information about this? I do not want to join the actual discussion about documentation of PostgreSQL but I never before have seen such a construct in SQL! Best regards, Jens Hartwig ============================================= Jens Hartwig --------------------------------------------- debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil : +49 (0)170 167-2648 E-Mail : jhartwig@debis.com =============================================
On Thu, 4 Jan 2001, Jens Hartwig wrote: > > SELECT a_nr, > > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, > > [...] > > FROM orders > > Is this a documented feature? Where can I find more information about > this? I do not want to join the actual discussion about documentation > of PostgreSQL but I never before have seen such a construct in SQL! Subqueries are covered in Bruce Momjian's book: http://www.postgresql.org/docs/aw_pgsql_book/node93.html I don't think the PostgreSQL User's Manual mentions sub-selects. -- Tod McQuillin
Tod McQuillin <devin@spamcop.net> writes: > I don't think the PostgreSQL User's Manual mentions sub-selects. I think you are right :-(. Geez, that's a pretty big hole in the docs coverage. There is some stuff in the Tutorial --- see http://www.postgresql.org/devel-corner/docs/postgres/sql-language.htm#SELECT and page down to "Subqueries". Note that the subquery-in-FROM feature is new in 7.1. regards, tom lane
> [...] > Subqueries are covered in Bruce Momjian's book: > http://www.postgresql.org/docs/aw_pgsql_book/node93.html > [...] Thanks for the information, but Bruce (which I have already read) and all other docs I´ve seen "only" mention normal subselects. I know subselects (what a poor database engineer I would be otherwise ;-)) but I have never seen a statement like SELECT a, (SELECT b) FROM xyz; IMHO this is no normal subselect and it does not correlate with the SQL-standard. Is it a trick? Best regards, Jens ============================================= Jens Hartwig --------------------------------------------- debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil : +49 (0)170 167-2648 E-Mail : jhartwig@debis.com =============================================
Jens Hartwig <jhartwig@debis.com> writes:
> I have never seen a statement like
> SELECT a, (SELECT b)
> FROM xyz;
> IMHO this is no normal subselect and it does not correlate with the
> SQL-standard. Is it a trick?
I think it's OK (we're assuming that a and b are columns of xyz, right?)
The inner select sees values from the outer select as constants. This
is not really different from
SELECT x FROM xyz WHERE y IN (SELECT a FROM abc WHERE b = xyz.z);
where the inner WHERE relies upon an outer reference to the current
xyz tuple.
It is true that "SELECT b" isn't a valid SQL92 SELECT because it
hasn't got a FROM clause, but that's a minor quibble that just about
every vendor has invented a workaround for.
regards, tom lane
Hello Tom,
> [...]
> > SELECT a, (SELECT b)
> > FROM xyz;
> [...]
> I think it's OK (we're assuming that a and b are columns of xyz, right?)
> [...]
immediately after having sent my message I realized my fault: a and b
are not of the same table! Correctly, the statement had to be something
like:
SELECT a, (SELECT b FROM z WHERE b = a) FROM x;
> [...]
> This is not really different from
> SELECT x FROM xyz WHERE y IN
> (SELECT a FROM abc WHERE b = xyz.z);
> [...]
Now it is :-) In a subquery, the inner query is only used for things
like comparison (as it is in your example). In my example the result
shows me two columns (in one record!!) which belong to different tables.
Mmmmh ... I tested the following:
create table x (a numeric); create table y (b numeric);
insert into x values (1); insert into x values (2);
insert into y values (1); insert into y values (2);
select a, (select b from y) from x;
=> ERROR: More than one tuple returned by a subselect used as an
expression.
This is ok, anything else would have shocked me.
select a, (select b from y where b = a) from x;
a | ?column? ----------+---------- 1.000000 | 1.000000 2.000000 | 2.000000
This result made me understanding that this special case of "subqueries"
is possibly nothing more than a special form of joins between tables:
select a, b from x, y where x.a = y.b;
brings the same result. Now, back to the first example (of Nikolaj):
SELECT a_nr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, (SELECT count(*) FROM cylinders WHERE
z_a_nr= a_nr AND z_status =
'zdr') AS #zdr, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
'zcu') AS #zcu, (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
'zcr') AS #zcr, product, state FROM orders;
This would be a self-join of one table like:
select ord.a_nr, c1.count(*), c2.count(*), ... from orders ord, cylinders c1,
cylinders c2, ... where c1.z_a_nr = ord.a_nr and c2.z_status = 'zdr' and ...
This in fact is not possible in PostgreSQL (it seems that the table
alias "c1" cannot be prefixed to the aggregate-function "count(*)") and
AFAIK in no other relational database. I really cannot imagine any
equivalent join-statement (or anything else like a subquery) which
brings the same results! Does this at all correlate with the philosophy
of a relational database?
Best regards, Jens
=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil : +49 (0)170 167-2648
E-Mail : jhartwig@debis.com
=============================================
On Thu, 4 Jan 2001, Jens Hartwig wrote: > This would be a self-join of one table like: > > select ord.a_nr, > c1.count(*), > c2.count(*), > ... > from orders ord, > cylinders c1, > cylinders c2, > ... > where c1.z_a_nr = ord.a_nr > and c2.z_status = 'zdr' > and ... > > This in fact is not possible in PostgreSQL (it seems that the table > alias "c1" cannot be prefixed to the aggregate-function "count(*)") That's true, and you can't say count(c1.*) either, but you *can* say count(c1.z_status)... Continuing from your example using tables x and y: # select count(x1.a) as ones, count(x2.a) as twos from x x1, x x2 where x1.a = 1 and x2.a = 2; ones | twos ------+------ 1 | 1 (1 row) But it doesn't do what you want: # insert into x(a) values(2); INSERT 313887 1 # select count(x1.a) as ones, count(x2.a) as twos from x x1, x x2 where x1.a = 1 and x2.a = 2; ones | twos ------+------ 2 | 2 (1 row) An inspection of the cartesian product (select x1.a, x2.a from x x1, x x2) will make it clear why it doesn't work. I can't think of any way to get this: ones | twos ------+------ 1 | 2 without subqueries like so: # select (select count(*) from x where a = 1) as ones, (select count(*) from x where a = 2) as twos; But, to answer your question, "Does this at all correlate with the philosophy of a relational database?" ... My answer is yes! After all, isn't it just the same as "select a, count(a) from x group by a" turned sideways? If you can think of how to do this "the hard way" (i.e. without subselects or temp tables etc.) please share. -- Tod McQuillin
I was playing around with subselects and managed to crash the backend (if you try, it can be done I guess): create table x (a numeric); create table y (b numeric); insert into x values (1); insert into x values (2); insert into x values (3); insert into x values (4); insert into x values (5); insert into x values (6); insert into x values (7); insert into x values (8); insert into x values (9); insert into x values (10); insert into x values (11); insert into y values (1); insert into y values (2); insert into y values (3); insert into y values (4); insert into y values (5); insert into y values (6); insert into y values (7); insert into y values (8); insert into y values (9); select a, (select b from y) from x; select a, (select b from y where b = a) from x; -- this is ok ... select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS bFROM x WHERE a IN (SELECT b FROM y)GROUP BY aHAVING a IN (SELECT b FROMy WHERE b > 1); a | b ----------+----------2.000000 | 3.0000003.000000 | 4.0000004.000000 | 5.0000005.000000 | 6.0000006.000000 | 7.0000007.000000| 8.0000008.000000 | 9.0000009.000000 | (8 rows) -- this crashes select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS bFROM x WHERE a IN (SELECT b FROM y)GROUP BY a,bHAVING a IN (SELECT bFROM y WHERE b > 1); pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !# \connect subselects No Postgres username specified in startup packet. !> \connect subselects pgcvs You are now connected to database subselects as user pgcvs. subselects=# This example is probably ridiculous and might break syntax!? Else maybe a bug in not liking "AS b". On Thursday 04 January 2001 06:13, Jens Hartwig wrote: > Hello Tom, > > > [...] > > > > > SELECT a, (SELECT b) > > > FROM xyz; > > > > [...] > > I think it's OK (we're assuming that a and b are columns of xyz, right?) > > [...] > > immediately after having sent my message I realized my fault: a and b > are not of the same table! Correctly, the statement had to be something > like: > > SELECT a, (SELECT b FROM z WHERE b = a) > FROM x; > > > [...] > > This is not really different from > > SELECT x FROM xyz WHERE y IN > > (SELECT a FROM abc WHERE b = xyz.z); > > [...] > > Now it is :-) In a subquery, the inner query is only used for things > like comparison (as it is in your example). In my example the result > shows me two columns (in one record!!) which belong to different tables. > Mmmmh ... I tested the following: > > create table x (a numeric); > create table y (b numeric); > > insert into x values (1); > insert into x values (2); > > insert into y values (1); > insert into y values (2); > > select a, (select b from y) from x; > > => ERROR: More than one tuple returned by a subselect used as an > expression. > > This is ok, anything else would have shocked me. > > select a, (select b from y where b = a) from x; > > a | ?column? > ----------+---------- > 1.000000 | 1.000000 > 2.000000 | 2.000000 > > This result made me understanding that this special case of "subqueries" > is possibly nothing more than a special form of joins between tables: > > select a, b > from x, y > where x.a = y.b; > > brings the same result. Now, back to the first example (of Nikolaj): > > SELECT a_nr, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > 'zdr') AS #zdr, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > 'zcu') AS #zcu, > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = > 'zcr') AS #zcr, > product, state > FROM orders; > > This would be a self-join of one table like: > > select ord.a_nr, > c1.count(*), > c2.count(*), > ... > from orders ord, > cylinders c1, > cylinders c2, > ... > where c1.z_a_nr = ord.a_nr > and c2.z_status = 'zdr' > and ... > > This in fact is not possible in PostgreSQL (it seems that the table > alias "c1" cannot be prefixed to the aggregate-function "count(*)") and > AFAIK in no other relational database. I really cannot imagine any > equivalent join-statement (or anything else like a subquery) which > brings the same results! Does this at all correlate with the philosophy > of a relational database? > > Best regards, Jens > > ============================================= > Jens Hartwig > --------------------------------------------- > debis Systemhaus GEI mbH > 10875 Berlin > Tel. : +49 (0)30 2554-3282 > Fax : +49 (0)30 2554-3187 > Mobil : +49 (0)170 167-2648 > E-Mail : jhartwig@debis.com > ============================================= -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
Syntax Rules 1) Each <column reference> in the <group by clause> shall unambiguously
referencea column of the table resulting from the <from clause>. A column referenced in a <group by clause>
is a grouping column. NOTE 101 - "Column reference" is defined in Subclause 6.6, "<column
reference>".
1) Every <column reference> has a qualifying table and a qualifying scope, as defined in succeeding
SyntaxRules.
The query I did was bad syntax I guess. That b only exists in the result
table, not in the table made in the FROM clause. Still, crashes so not good.
subselects=# select a, (SELECT b FROM y WHERE y.b = x.a + 1) from x WHERE a
IN (SELECT b FROM y) GROUP BY a, b HAVING a IN (SELECT b FROM y WHERE y.b >
1);
ERROR: Attribute 'b' not found
subselects=#
That's what should be expected (right?) and I get it if I remove "AS b".
On Monday 08 January 2001 17:24, Robert B. Easter wrote:
> I was playing around with subselects and managed to crash the backend (if
> you try, it can be done I guess):
>
> create table x (a numeric);
> create table y (b numeric);
>
> insert into x values (1);
> insert into x values (2);
> insert into x values (3);
> insert into x values (4);
> insert into x values (5);
> insert into x values (6);
> insert into x values (7);
> insert into x values (8);
> insert into x values (9);
> insert into x values (10);
> insert into x values (11);
>
> insert into y values (1);
> insert into y values (2);
> insert into y values (3);
> insert into y values (4);
> insert into y values (5);
> insert into y values (6);
> insert into y values (7);
> insert into y values (8);
> insert into y values (9);
>
> select a, (select b from y) from x;
> select a, (select b from y where b = a) from x;
>
> -- this is ok ...
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a
> HAVING a IN (SELECT b FROM y WHERE b > 1);
> a | b
> ----------+----------
> 2.000000 | 3.000000
> 3.000000 | 4.000000
> 4.000000 | 5.000000
> 5.000000 | 6.000000
> 6.000000 | 7.000000
> 7.000000 | 8.000000
> 8.000000 | 9.000000
> 9.000000 |
> (8 rows)
>
> -- this crashes
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a,b
> HAVING a IN (SELECT b FROM y WHERE b > 1);
>
> pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !# \connect subselects
> No Postgres username specified in startup packet.
> !> \connect subselects pgcvs
> You are now connected to database subselects as user pgcvs.
> subselects=#
>
> This example is probably ridiculous and might break syntax!? Else maybe a
> bug in not liking "AS b".
>
> On Thursday 04 January 2001 06:13, Jens Hartwig wrote:
> > Hello Tom,
> >
> > > [...]
> > >
> > > > SELECT a, (SELECT b)
> > > > FROM xyz;
> > >
> > > [...]
> > > I think it's OK (we're assuming that a and b are columns of xyz,
> > > right?) [...]
> >
> > immediately after having sent my message I realized my fault: a and b
> > are not of the same table! Correctly, the statement had to be something
> > like:
> >
> > SELECT a, (SELECT b FROM z WHERE b = a)
> > FROM x;
> >
> > > [...]
> > > This is not really different from
> > > SELECT x FROM xyz WHERE y IN
> > > (SELECT a FROM abc WHERE b = xyz.z);
> > > [...]
> >
> > Now it is :-) In a subquery, the inner query is only used for things
> > like comparison (as it is in your example). In my example the result
> > shows me two columns (in one record!!) which belong to different tables.
> > Mmmmh ... I tested the following:
> >
> > create table x (a numeric);
> > create table y (b numeric);
> >
> > insert into x values (1);
> > insert into x values (2);
> >
> > insert into y values (1);
> > insert into y values (2);
> >
> > select a, (select b from y) from x;
> >
> > => ERROR: More than one tuple returned by a subselect used as an
> > expression.
> >
> > This is ok, anything else would have shocked me.
> >
> > select a, (select b from y where b = a) from x;
> >
> > a | ?column?
> > ----------+----------
> > 1.000000 | 1.000000
> > 2.000000 | 2.000000
> >
> > This result made me understanding that this special case of "subqueries"
> > is possibly nothing more than a special form of joins between tables:
> >
> > select a, b
> > from x, y
> > where x.a = y.b;
> >
> > brings the same result. Now, back to the first example (of Nikolaj):
> >
> > SELECT a_nr,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zdr') AS #zdr,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zcu') AS #zcu,
> > (SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status =
> > 'zcr') AS #zcr,
> > product, state
> > FROM orders;
> >
> > This would be a self-join of one table like:
> >
> > select ord.a_nr,
> > c1.count(*),
> > c2.count(*),
> > ...
> > from orders ord,
> > cylinders c1,
> > cylinders c2,
> > ...
> > where c1.z_a_nr = ord.a_nr
> > and c2.z_status = 'zdr'
> > and ...
> >
> > This in fact is not possible in PostgreSQL (it seems that the table
> > alias "c1" cannot be prefixed to the aggregate-function "count(*)") and
> > AFAIK in no other relational database. I really cannot imagine any
> > equivalent join-statement (or anything else like a subquery) which
> > brings the same results! Does this at all correlate with the philosophy
> > of a relational database?
> >
> > Best regards, Jens
> >
> > =============================================
> > Jens Hartwig
> > ---------------------------------------------
> > debis Systemhaus GEI mbH
> > 10875 Berlin
> > Tel. : +49 (0)30 2554-3282
> > Fax : +49 (0)30 2554-3187
> > Mobil : +49 (0)170 167-2648
> > E-Mail : jhartwig@debis.com
> > =============================================
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
"Robert B. Easter" <reaster@comptechnews.com> writes: > The query I did was bad syntax I guess. No, it's just a bug. PG accepts GROUP BY items that are references to output columns, cf. http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm Looks like it gets confused when the grouped-by item contains a subselect, however. regards, tom lane
"Robert B. Easter" <reaster@comptechnews.com> writes:
> -- this crashes
> select a, (SELECT b FROM y WHERE y.b = x.a + 1) AS b
> FROM x WHERE a IN (SELECT b FROM y)
> GROUP BY a,b
> HAVING a IN (SELECT b FROM y WHERE b > 1);
Fixed. 'Twas another case of trying to close a subplan twice ...
regards, tom lane