Page 1 of 1

GROUP BY an unknown field value

Posted: Thu Oct 09, 2008 8:43 pm
by s.dot
Hey guys, consider the following (mysql 5.0+) SQL query (which has been anonymized for privacy purposes)

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'
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:

Code: Select all

aa   |  bb   |  c
---------------------------
123       456        domain1.com
153       3829      domain2.com
72         283       domain3.com
112       8559      domain4.com
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?

Re: GROUP BY an unknown field value

Posted: Fri Oct 10, 2008 5:20 am
by VladSun
scottayy wrote: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.
Create a temporary table with all of your domains to search for (e.g. with column `domain`), JOIN it on domain substring found and use GROUP BY `domain`.
Also, I think your first query is a little bit wrong - because in fact it is :
[sql]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'    )[/sql]