>Dear Experts...
>I just upgraded to VFP 9.0, where the syntax for the GROUP BY clause has changed and requires to list all the fields. I am new to su-queries.
> I want to select multiple fields from a table but SUM & GROUP BY by field (itm_nuymb) only. Can someone give me a proper syntax to accomplish that. Listed below is a sample code which I think need a sub-query and I am new to that.
>
>
> SELECT itm_numb, itm_desc, sls_code, tax_code, SUM(unt_qtty) AS nQty ;
> FROM table1 GROUP BY itm_numb ORDER BY itm_numb
>
>
>Thank you in advance.
>
>Raza Malik
No subquery needed for this one. Just include all the fields that are not part of the aggregate expression ( the SUM() )
SELECT itm_numb, itm_desc, sls_code, tax_code, SUM(unt_qtty) AS nQty ;
FROM table1 ;
GROUP BY itm_numb,itm_desc, sls_code, tax_code ORDER BY itm_numb
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.