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.
--sb--