[RESOLVED] Aggregate SUM on variable group by

dagon

I have imported data to process:

id |money

1 1
1_1 7
1_2 7
1_3 6
2 3
3 5
4 8
4_1 9

i need to sum when the id has an underscore, groping by the value before the underscore

so from the above i would get

1 20
4 9

suggestions, welcome.

Weedpacket

Something like "Select substring(id) as prefix, sum(money) group by substring(id)", perhaps? Whatever the appropriate arguments (and function name) for substring() would be. I guess that would end up with results for the rows with no underscore in the ID (depending on what the substring turns out to be in that case), in which case there'd need to be a filter.

dagon

thanks, weedpacket, That was the general direction i was headed, what i need to end up doing with this data seems a movable feast, probably end up all in php, will post a solution to the specific issue above when i complete one, for any who find the thread in the future

johanafm

SELECT SUBSTRING(id, 1, INSTR(id, '_')) AS prefix, SUM(money)
FROM table
WHERE id = '&#37;_%'
GROUP BY prefix

dagon

thanks johanafm, needed a couple of tweaks on your code, but its now working as expected

 SELECT SUBSTRING( id 1, (INSTR( id, '_' ) -1 )) AS prefix, SUM( money )
FROM table
WHERE id LIKE '%\_%'
GROUP BY prefix

couple of notes:

INSTR -1 so to remove the , works with it as previous posted but the ids are ###

escaped underscore in search as its a wild card along with% in mysql.