SQL Statement Help needed
I know someone out there has enough sql knowledge to help me get this done.
Environment:
Mysql
Relevant Table fields:
DATETIME DOMLKDATE
TEXT DOMAINNAME
INT DOMRETCODE
TEXT DOMIP
Every time a domain name is looked up on my system, I log an entry in the table.
I want to tell what my sign-up to User-lookup ratio is. IE: 5 users looked up 7 available domains should result in 5. (meaning I have 5 unique users looking for a domain). I'll compare this data with my sign-up rate. I would like to do this in a single query statement.
I want a single line per day returned like this.
Year Day Month LookupCount
2003 3 6 8
I've played with several statements, but I can't seem to tie it down. Any help is appreciated.
SELECT DISTINCT
YEAR(DOMLKDATE) AS Y,
MONTH(DOMLKDATE) AS M,
DAYOFMONTH(DOMLKDATE) AS D,
COUNT(*) as lkupcnt FROM `DOMLOOKUPS` WHERE
DOMRETCODE` = 0
GROUP BY Y,M,D,DOMIP,DOMDOMAIN
