Re: MYSQLI and GROUP BY help - please

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 <[email protected]> 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.
>