Sergey, you're still a SQL-genius. Thanks!


>John,
>
>Try
>

>SELECT ig.ItemGroup, SUM(Cost) AS SumCost
>	FROM ( SELECT Item AS ItemGroup FROM po p1
>			WHERE NOT EXISTS ( SELECT 1 FROM po
>				WHERE p1.Item <> Item AND p1.Item LIKE Item + '%')
>		) ig JOIN po ON po.Item LIKE ig.ItemGroup + '%'
>	GROUP BY ig.ItemGroup
>

>
>>
>>I keep thinking there should be an easy way to query for what I need but just can't come up with anything so here it goes.
>>
>>In a typical accounting PO line-item table which stores item number and cost, I need to sum the cost by highest group item number. For example, if I have data similar to show below:
>>
>>
>>Item # Item Cost
>>----------------- ---------------
>>ABC 10.00
>>ABC1 20.00
>>ABC23 40.00
>>ABC456 50.00
>>XYZ 11.00
>>XYZ1 22.00
>>XYZ23 33.00
>>XYZ456 44.00
>>etc..
>>
>>I want a group total that returns the "shortest item #" with associated sum of the costs like so:
>>
>>Item # Item Cost
>>----------------- ---------------
>>ABC 120.00
>>XYZ 110.00
>>
>>The tricky part is that the length of the shortest item # in a group is not constant. It would be very easy if it was. Any ideas? TIA.