db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Group By - problems
Date Mon, 05 Feb 2007 22:33:33 GMT
Hi Diego,

The message is correct and Derby is behaving as it should.

If you are using GROUP BY, you are aggregating a number of rows into  
a smaller number of rows, so instead of e.g. 100 rows all of which  
contain the same clienti.provincia, you aggregate all 100 rows into a  
single row. In order to do this, you need to either propagate the  
information you want (the GROUP BY items) or aggregate them using one  
of the aggregate functions MIN, MAX, AVG, SUM, etc.

So you could e.g. aggregate clienti.birthday to find the youngest and  
oldest clienti in each provincia using SELECT provincia, MIN 
(clienti.birthday), MAX(clienti.birthday) FROM clienti GROUP BY  
clienti.provincia but this doesn't sound like what you are trying to do.

 From the looks of the query, you don't want the GROUP BY clause at  
all...

Craig

On Feb 5, 2007, at 1:31 PM, Diego Zanga wrote:

> lo,
>
> I'm using a query developed for mysql.
> actually it works with derby only IF there is NO <group by clienti.id>
> ------
> select clienti.id , clienti.cognome , clienti.nome , clienti.display ,
> clienti.idtitolo, wk7.descrizione , clienti.indirizzo , clienti.citta
> , clienti.provincia , clienti.cap , clienti.idnazione,
> wk13.description , clienti.pi , clienti.codfis ,
> clienti.idlegalerappresentante, wk17.display , clienti.birthday ,
> clienti.born_prov , clienti.born_city, wk21.codicearchivio ,
> clienti.note , clienti.phone1 , clienti.phone2 , clienti.phone3 ,
> clienti.home_phone , clienti.fax , clienti.mobile1 , clienti.mobile2 ,
> clienti.email1 , clienti.email2 , clienti.email_pec , clienti.fido ,
> clienti.esposizione , clienti.tariffa_oraria  from clienti left outer
> join subcliente on clienti.id = subcliente.idcliente left outer join
> pratiche as wk21 on wk21.id = subcliente.idpratica left join
> legale_rappresentante_cliente as wk17 on  wk17.id =
> clienti.idlegalerappresentante  left join nazione as wk13 on  wk13.id
> = clienti.idnazione  , titoli as wk7  group by clienti.id  order by
> cognome,nome
> --------
>
> the query is <developed> by an engine, and is one of the smallest :OP
>
> the error is:
> java.sql.SQLException: The SELECT list of a grouped query contains at
> least one invalid expression. If a SELECT list has a GROUP BY, the
> list may only contain valid grouping expressions and valid aggregate
> expressions.
>
> I dont understand how the select could hold one invalid expression if
> without the <group by> it works: there's even no aggregate expression,
> so no <invalid> ones....
>
> any idea?
>
>
> cya, thx
> -- 
> Diego Zanga
> ------------------------------
> http://www.eLawOffice.it
> http://www.blogstudiolegale.eu
> http://www.javablog.eu
> Skype NAARANI

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message