Fwd: MYSQLI and GROUP BY help - please

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



http://sqlfiddle.com/#!9/857486/3

Do you see my setup code etc? (I haven't used any fiddles before).

I DO notice that on JSFIDDLE when using this code, i get the LATESt
date for colony 8. (17th). but on my main website MYSQL (shared
server) I'm getting the16th for the same data & sane SQL command.


SELECT * FROM
(SELECT * FROM `dailycolonystats` ORDER BY `Date`  DESC) as sub
WHERE `Region` = 'AUCKLAND'
GROUP BY Grp, Colony, Type;

Only colony 8 has 2 sets of dates - The rest have 1 set of dates /
figures so far...

My main shared server has this version -

SELECT VERSION() = 5.6.47-87.0-log





On Fri, 26 Aug 2022 at 12:34, Aziz Saleh <azizsaleh@xxxxxxxxx> wrote:
>
> Can you add the schema and sample inserts here and send a link (makes it easier to debug on the run): http://sqlfiddle.com
>
> My assumption is that you want to do the grouping in the inside select, but there might be a better way of doing it if you can give a sample DDL/inserts to play around with.
>
> On Thu, Aug 25, 2022 at 4:49 PM <gordonisnz@xxxxxxxxx> wrote:
>>
>> Hello. I've got a pickle with the MYSQLI GROUP BY and wondering if
>> anyone can assist.
>>
>> basically, I've got a small report/query that extracts data, but then
>> I go into a foreach loop & "manually" extract only the latest / most
>> recent data out of it, & create an array. This seems to work.
>>
>> I'm now on a REGION report 9extracts a lot more data, once things are
>> in place), so I would rather the SQL command be correct to extract
>> only the latest data in the first instance - rather than manually
>> filtering out the data.
>>
>> I've got a table with the following fields
>>
>> - Region
>> - Group  (number)
>> - Colony (number)
>> - type  (cats or kittens)
>> - date   (yyyymmdd)
>> - number cats (a number - number of cats/kittens in that colony)
>> - number kittens (a number - number of cats/kittens in that colony)
>>
>> My current / latest SQL is:
>>
>> SELECT * FROM
>> (SELECT * FROM `dailycolonystats` ORDER BY `Date`  DESC) as sub
>> WHERE `Region` = 'AUCKLAND'
>> GROUP BY Grp, Colony, Type ;"
>>
>> It seems to be working OK, EXCEPT the total is wrong.
>>
>> If I have 3 rows of information
>>
>> CITY, 999, 1, cats, 20220815, 5
>> CITY, 999, 3, cats, 20220815, 7
>> CITY, 999, 3, cats, 20220818, 5
>>
>> Colony 3, had 7 kittens on 20220815 (15 Aug 2022). However, on the
>> 18th, it dropped to 5.
>>
>> The results I'm getting include the 15th of august for colony 3. and
>> not the 18th.
>>
>> I've been working on this for the last few days and it's annoying me.
>>
>> Is there a way to alter the SQL to Group by the city/group/colony
>> number, and type?
>>
>> Then Sort by date DESCENDING and only use the latest date for each
>> group. (i know the GROUP BY uses the first result it sees)
>>
>> PS - I can't sort by date first, as, in the above example, Colony 3
>> has 18th August, but colony 1 doesn't have an entry of that date.
>>
>> Any advice?
>>
>>
>>
>>
>> --
>>
>> Gordon.



--

Gordon.


-- 

Gordon.



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux