GROUP BY an unknown field value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

GROUP BY an unknown field value

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: GROUP BY an unknown field value

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply