List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:February 7 2012 7:28pm
Subject:strange select/join/group by with rollup issue....
View as plain text  
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.
awallace@stripped
(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.
Thread
strange select/join/group by with rollup issue....Andy Wallace7 Feb
  • Re: strange select/join/group by with rollup issue....Arthur Fuller8 Feb
    • Re: strange select/join/group by with rollup issue....Andy Wallace8 Feb