How do you get 'group by' in MySQL to work with LIKE wildcards?

[smithaa02]

The fields for my table are count, date, and website.

What I want to do is to get a count summary for each date for websites that contain the text 'mysite.com'.

So I do...:

SELECT date, sum(count)
FROM my_table
WHERE website LIKE 'mysite.com%'
GROUP BY date
ORDER BY date ASC

And it does not group correctly as I get multiple duplicate dates. I did some fiddling and found out group by will work correctly without using like, so I believe that is the problem.

Does anybody have any ideas why this is happening?

[syber]

This should not be happening. The WHERE clause simply limits what detail gets grouped, using LIKE or any other predicate in the WHERE clause should have no effect on the grouping. Are you sure that you don't have a column besides date and sum(count) in the column list and GROUP BY list? That would cause the behavior you are experiencing.

[smithaa02]

I actually do have an additional column 'referrer' that I also use filter out with a conditional where, but that shouldn't make a difference, right?

I think this might be a timestamp issue...

For those curious, here is how to reproduce what I'm having issues with:

DUMP:
---------------------------------------------------
CREATE TABLE `my_table` (
`website` text NOT NULL,
`referrer` text NOT NULL,
`date` timestamp(8) NOT NULL,
`count` text NOT NULL
) TYPE=MyISAM;

INSERT INTO `my_table` (`website`, `referrer`, `date`, `count`) VALUES ('mysite.com', 'referrer2.com', '20060124', '1');
INSERT INTO `my_table` (`website`, `referrer`, `date`, `count`) VALUES ('mysite.com/index.htm', 'referrer.com', '20060124', '1');
---------------------------------------------------

QUERY 1: (notice this groups correctly!)
---------------------------------------------------
SELECT date, sum( count )
FROM my_table
WHERE website LIKE 'mysite.com%'
GROUP BY date
---------------------------------------------------

UPDATE ROW:
---------------------------------------------------
UPDATE `my_table` SET `referrer` = 'referrer3.com',
`date` = NOW( ) WHERE `website` = 'mysite.com' AND `referrer` = 'referrer2.com' AND `date` = '20060124' AND `count` = '1' LIMIT 1 ;
---------------------------------------------------

QUERY 2: (now it doesn't work!)
---------------------------------------------------
SELECT date, sum( count )
FROM my_table
WHERE website LIKE 'mysite.com%'
GROUP BY date
---------------------------------------------------

Is this an issue with the timestamp fields?

[LifeinAsia]

I suspect that the Now() fuction is adding hours, minutes, seconds, etc. to the 'date' field, making it a completely different value than the previous inputs.

Two other points:
1) It's bad style to use reserved words (like 'date' and 'count') for column names (I guess mySQL is more forgiving than other DBs)- it can really mess you up later.
2) I take it 'count' is going to be a couter of th enumber of referers for each site? Making it an integer type will make life much easier in the future.

[smithaa02]

I suspect that the Now() fuction is adding hours, minutes, seconds, etc. to the 'date' field, making it a completely different value than the previous inputs.

That's what I suspected too, but this shouldn't happen because I created the date field as timestamp(8)...right? Does anybody know how this works?

1) It's bad style to use reserved words (like 'date' and 'count') for column names (I guess mySQL is more forgiving than other DBs)- it can really mess you up later.

Yeah, I probably shouldn't be using these keywords, but this shouldn't make a difference in MySQL, correct?

2) I take it 'count' is going to be a couter of th enumber of referers for each site? Making it an integer type will make life much easier in the future.

This was just a quick demo table and I missed that. It doesn't affect the grouping quirk though...

[chrisjoha]

You are not using the timestamp the correct way. As mentioned, now() inserts more than just year, month and day. If you want to group on those values try this:

SELECT date_format(date, '%Y%m%d'), sum(count)
FROM my_table
WHERE website LIKE 'mysite.com%'
GROUP BY date_format(date, '%Y%m%d')
ORDER BY date ASC

But seriously, change the names of those columns! :)

[smithaa02]

Thank you to everybody that responded. It was indeed the timestamp issue that was fixed by using the date functions as suggested by Chris.

Anybody know if this is a bug/quirk with my version of MySQL (4.0.18) that is fixed with later versions?

[LifeinAsia]

What bug? That's exactly how timestamp() is supposed to work- return the exact time at that point. The accuracy involved depends on the type and version of SQL being used, but I assume mySQL probably goes down to miliseconds.

When you hard code a value into a timestamp field, the database fills in zeros for the rest of the precision. For example, you though you were entering "20060124" but the data was stored as something like "20060124.00000000" (or however it's represented in mySQL). Doing the Now() would only be able to enter that exact value if the computer computed the command at that exact milisecond (or whatever precision was involved). More likely, it would enter something like "20060124.000003412" which is completely different from "20060124.00000000" from a database standpoint.

To do what you are apparently trying to do, you need to use the code suggested or change your field type to something with less precision. I assume mySQL has a date type that only inlcudes years/months/days, not hours/minutes?

[smithaa02]

To do what you are apparently trying to do, you need to use the code suggested or change your field type to something with less precision. I assume mySQL has a date type that only inlcudes years/months/days, not hours/minutes?

Both the date and timestamp should have this functionality. You just specify the number of date digits you want to store {like timestamp(8) which I did} and that should do the trick, but apparently timestamp field is storing more then the 8 digits yyyymmdd...

[LifeinAsia]

I could be wrong, but I think the 8 refers to the level of precision, not the number of digits. mySQL is storing the field as a timestamp datatype, not a char type.

As an example, "123" as a char type is not stored the same way as "123" as a varchar type or "123" and an integer type and certainly not the same as "123" as a real type.

Now, if you really wanted to (ick!), you COULD convert the Now() result into an 8-character string and save it that way into an 8-character field in the database. Then you can easily do your grouping on the field and all datestamps for Jan. 24, 2006 will show as '20060124' but you lose any date-related functionality inherent with time/date-related character types. From a performance side, string-based searches are much slower.

Google "timestamp type in mysql" for some good links.