General information
Forum:
Microsoft SQL Server
>>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>
Used to. At least they finally fixed it.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only