TalkPHP - View Single Post

delayedinsanity · 11-09-2009, 10:40 PM

Tried them, with the following basic query to test it;

mysql Code:

SELECT SUBSTR( timestamp, 1, 10 ) AS timestamp,
CASE WHEN spider = '' THEN COUNT( DISTINCT ip ) END AS this,
CASE WHEN spider <> '' THEN COUNT( DISTINCT ip ) END AS that
FROM `table`
GROUP BY SUBSTR( timestamp, 1, 10 )

But I just get null values in column two for row one, null in column three for row two, and so on down the line. Seems like it should work though, but the null values are counter-productive. I should be getting values for both columns on any given date (the database is populated with test data, and running the queries seperately produces the right numbers, so it isn't just because it can't find any data based on the WHERE clause).

11-09-2009, 10:40 PM	#13 (permalink)
delayedinsanity is cute and cuddly Join Date: Mar 2008 Location: Vegas, Baby Posts: 963 Thanks: 31	Tried them, with the following basic query to test it; mysql Code: SELECT SUBSTR( timestamp, 1, 10 ) AS timestamp, CASE WHEN spider = '' THEN COUNT( DISTINCT ip ) END AS this, CASE WHEN spider <> '' THEN COUNT( DISTINCT ip ) END AS that FROM `table` GROUP BY SUBSTR( timestamp, 1, 10 ) But I just get null values in column two for row one, null in column three for row two, and so on down the line. Seems like it should work though, but the null values are counter-productive. I should be getting values for both columns on any given date (the database is populated with test data, and running the queries seperately produces the right numbers, so it isn't just because it can't find any data based on the WHERE clause).