you can see what im trying to do here:
select *, count(*) as count
from `songs`
where `band` REGEXP '^[^[:alpha:]]'
group by `band`
order by `band` asc
bands can be:
avenged sevenfold
3 days grace
led zeppelin
98 mute
back street boys
beastie boys
i need this to select the bands whose first-character is not an alpha, and count how many rows exist for each band.
unfortunately my current query just seems to group all of them together that match the REGEXP.
You can't select columns that are not on the group by clause neither are a group function (count, max...)
The where it's ok because you don't need to group unneed rows and the condition is not over the group value (the result of a group function).
ASC is the default sort sense, so you don't need to specify it.
select band, count(*) as count
from songs
where band REGEXP '^[^[:alpha:]]'
group by band
order by band
Does doing the selection after the group help?
select `band`, count(*) as count
from `songs`
group by `band`
having `band` REGEXP '^[^[:alpha:]]'
order by `band` asc
Also you appear to be selecting columns that aren't in the group clause. Try:
select `band`, count(*) as count
from `songs`
where `band` REGEXP '^[^[:alpha:]]'
group by `band`
order by `band` asc
