pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: De Moudt Walter <wdemoudt@planetinternet.be>
To: Max Buvry <Max.Buvry@enseeiht.fr>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] subquery, except and view
Date: Thu, 09 Dec 1999 03:11:59 +0100
Message-ID: <384F0FEF.1BF044B1@planetinternet.be> (raw)
References: <199912071443.PAA11193@enseeiht.enseeiht.fr>

Hi Max,

This worked for me :

-first create a view that displays the distinct clients with their
amount of occurence in the command table, then use a querry on both the
view and the clients table :

first the view :

test :>create view commamount as
test :>select num_clt, count(*) as howmuch
test :>from command
test :>group by num_clt;
CREATE

test :>select client.num_clt, client.name from client, commamount where
test :>(client.num_clt = commamount.num_clt) and
test :>(3 > howmuch);

This seems to do the job perfectly. Notice the value preceeding the
column name in the comparison. Seems nescesary ...

Didn't test the second example. This does the trick anyway :-)

Greetings,

Walter De Moudt

Max Buvry wrote:
> 
> 
> Hi,
> 
> Please, I need help to correct some sql queries...
> 
> Let me define my db :
> 
> CLIENT (NUM_CLT, NAME,   ADR)
> ITEM   (NUM_IT,  NAME,   PRICE)
> COMMAND(NUM_CLT, NUM_IT, QT)
> 
> I meet two problems : one concerns subqueries or
> operator instruction "except" and the other, the view.
> 
> I don't understand why the two queries below
> are not correct with Postgresql though it be correct with Oracle
> (I want to know the clients which have not
> made more than 2 commands) :
> 
> -------
> QUERY 1
> -------
> 
> select clt.num_clt
> from client clt
> where clt.num_clt not in
>    (select cd.num_clt
>     from commande cd
>     group by cd.num_clt
>     having count(*) > 2);
> 
> The PostgreSQL Error is : rewrite: aggregate column
>   view must be at rigth side in qual
> 
> I test the subquery and it is ok.
> 
> 
> -------
> QUERY 2
> -------
> 
> select clt.num_clt
> from client clt
> except
>    select cd.num_clt
>    from command cd
>    group by cd.num_clt
>    having count(*) > 2;
> 
> The PostgreSQL Error is : parser: parse error at or next except
> 
> If I use "(" and ")" for the subquery, the PostgreSQL Error
> is : parser: parse error at or next select.
> 
> I try to check "except" with simple query and I don't succeed it.
> 
> -------
> QUERY 3
> -------
> 
> I want to create a view and I want to name the column.
> I don't find the exact syntax for this.
> 
> create view payment( NAME_C, TT ) as
>    select client.name NAME_C, sum(qt*prix) TT
>    from client, item, command
>    where client.num_clt=command.num_clt and item.num_item=command.num_item
>    group by client.name, client.num_clt
> 
> An other question : why the num_clt is selected also ?
> 
> 
> If I don't name the column, the query is correct and Postgres
> chooses the names sum in place of TT.
> (select * from payment returns the correct result) :
> 
> create view payment as
>    select client.name, sum(qt*prix)
>    from client, item, command
>    where client.num_clt=command.num_clt and item.num_item=command.num_item
>    group by client.name, client.num_clt
> 
> But when I want to know the client which must pay more than 1000 :
> 
> select * from payment
> where sum>1000
> 
> The PostgreSQL Error is : rewrite: aggregate column
>   view must be at rigth side in qual
> 
> In advance, thank you for your help.
> 
> mb
> 
> ************




view thread (5+ messages)  latest in thread

Message-ID: <384F0FEF.1BF044B1@planetinternet.be>
Permalink:  ../384F0FEF.1BF044B1@planetinternet.be/
Also on:    postgresql.org/message-id/384F0FEF.1BF044B1@planetinternet.be

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: wdemoudt@planetinternet.be, Max.Buvry@enseeiht.fr
  Subject: Re: [SQL] subquery, except and view
  In-Reply-To: <384F0FEF.1BF044B1@planetinternet.be>
  References: <199912071443.PAA11193@enseeiht.enseeiht.fr> <384F0FEF.1BF044B1@planetinternet.be>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox