GROUP BY an unknown field value
Posted: Thu Oct 09, 2008 8:43 pm
Hey guys, consider the following (mysql 5.0+) SQL query (which has been anonymized for privacy purposes)
That part is fine. However I would like to GROUP BY `c`, which is rather hard because `c` is being compared using LIKE and the % character.
I'd like my results to look like this:
I notice there's a REGEXP and REPLACE function but no combination of them, nor ability to capture regexp matches.
Is what I'm trying to do possible, or a similar functionality available?
Code: Select all
SELECT
COUNT(DISTINCT `a`) AS `aa`,
COUNT(*) AS `bb`
FROM
`mytable`
WHERE
(`c` LIKE '%domain1.com%/')
OR
(`c` LIKE '%domain2.com/%')
OR
(`c` LIKE '%domain3.com/%')
OR
(`c` LIKE '%domain4.com/%')
AND
`d` >= '1222997561'I'd like my results to look like this:
Code: Select all
aa | bb | c
---------------------------
123 456 domain1.com
153 3829 domain2.com
72 283 domain3.com
112 8559 domain4.comIs what I'm trying to do possible, or a similar functionality available?