I am having a problem with select results that I don't understand. It seems to
be tied up with a GROUP BY statement. Forgive the complexity of the SQL, I
inherited some problematic data structuring.
If I use this statement:
SELECT lu_rcode_bucket.bucket AS 'BUCKET',
CP_PKG.value AS 'PRODUCT',
CP_PKG.value 'PACKAGE',
client.active AS 'ACTIVE',
client.created AS 'CREATED',
count(*) as 'CNT'
FROM client
JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item =
'rcode'
JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1
JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg'
LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND
CP_IDX_PKG.item = 'IDX_PKG'
WHERE client.created >= '2012-02-07' AND client.created <= '2012-02-07'
GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP
I get what I expect, having a number of rows where the "client.created" date is
2012-02-07.
But if I change it to this (the only change is the "from" date):
SELECT lu_rcode_bucket.bucket AS 'BUCKET',
CP_PKG.value AS 'PRODUCT',
CP_IDX_PKG.value 'PACKAGE',
client.active AS 'ACTIVE',
client.created AS 'CREATED',
count(*) as 'CNT'
FROM client
JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item =
'rcode'
JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1
JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg'
LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND
CP_IDX_PKG.item = 'IDX_PKG'
WHERE client.created >= '2012-02-01' AND client.created <= '2012-02-07'
GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP
The results contain no data with "client.created" = 2012-02-07. If I get rid of
the group
by (and the count(*)), there are rows with all 7 dates. I have tried changing the
"to"
date from '2012-02-07' to '2012-02-08', in case this was a "less than" issue,
but that doesn't
change.
Why is the group by dropping the last date of my data?
thanks,
andy
--
Andy Wallace
iHOUSEweb, Inc.
awall...@ihouseweb.com
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
