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.
It's "my" world. You're just living in it.