sql syntax

[filipe26]

Hi.Im trying to make a query on acess like this:

Select * from inverno
group by ano
order by cliente;

i get an error message saying that:

"Is not possible groupinf fields selected by *"

Why?Thanks

 

[kingz2000]

just list the fields of the table and it should work

 

[r937]

if you use GROUP BY, then your SELECT list will have a mix of aggregate expressions like COUNT() and SUM(), as well as non-aggregate expressions or columns

all non-aggregate expressions must then also be listed in the GROUP BY

so to answer your question, if you use "select star" then you must also list all columns in the GROUP BY -- and you cannot use the asterisk in the GROUP BY, you have to list the columns individually

r937.com | rudy.ca

 

[filipe26]

Ok now i have:

select cliente,ano
from inverno
group by ano;

and i get

"You are trying to execute a query with the specified experession 'cliente' not included as a part of ana agregation function

 

[r937]

select cliente,ano
from inverno
group by cliente,ano

r937.com | rudy.ca

 

[gwinn7]

Remove the GROUP BY.

Again, as the prior post stated, the GROUP BY clause is only used when you have aggregate functions like SUM, COUNT, MAX, etc in your SELECT clause like...

SELECT myID, SUM(myamount) as Amt FROM MyTable GROUP BY myID

So, in your case...

SELECT client, ano
FROM inverno

Gary
gwinn7

 

[r937]

just a very small correction --

i did not say GROUP BY is only used when you have aggregate functions

you don't actually have to have any aggregate functions in order to use GROUP BY

the mix of aggregate and non-aggregate in the SELECT can be some of each, or all of one and none of the other

hope that's not too confusing

r937.com | rudy.ca