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.
