>Given this data:
>ITEM DESCRIPT STYLE
>aaa Item aaa 1
>bbb Item bbb 1
>ccc Item ccc 2
>ddd Item ddd 3
>eee Item eee 2
>
>A pre-VFP 8.0 SQL statement such as:
>SELECT itemno, descript, styleid, count(item) AS count
> GROUP BY styleid ;
> ORDER BY styleid ;
> INTO CURSOR xxx
>
> You get the following result:
>ITEM DESCRIPT STYLE COUNT
>bbb Item bbb 1 2
>eee Item eee 2 2
>ddd Item ddd 3 1
>
>But this won't work in MS SQL Server. It generates an error "Invalid Group By -- all select fields must be used in an aggregate function."
>
>So if I wantto simulate the result of the above VFP SQL statement in MS SQL Server, how do I do that? What combination of SQL Statements or computed columns or whatever do I need??
>
>Joel
In an aggregate function any column not part of the calculation or part of the group by is garbage data. If there is a 1-1-1 relationship between the itemno - descript - style column use them all as the group by statement. If a style has more than one item, the item column is meaningless as it could be the item in any one of the rows.
VFP's sloppy implementation of group by just lets people do things they shouldn't do <g>
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.