Re: [SQL] subquery, except and view - Mailing list pgsql-sql

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
> 
> ************