MYSQLI and GROUP BY help - please

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

 



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.



[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