MYSQL Query Value Different

General Discussion
1

I'm doing a query and the values coming back are not whats in the table..

So for example I perform this query..

SELECT ID, Serial, ProcessHours, MAX(t_stamp)
FROM machine_data
WHERE Type = 'BLAST'
group by Serial

It returns..

|29|BB-002|78|2018-12-20 14:37:49|
|271|BB-F18-001|0|2018-11-28 02:05:05|
|549|BB-F18-002|0|2019-01-24 14:00:52|
|593|BB-003|117|2019-02-04 18:47:14|
|2,071|BB-001|0|2019-02-04 18:47:14|

But take ID 593.. The actual line in the table is..

593 BB-003 117 2018-12-18 16:37:57

Why does the query bring back a different date from the table.

The query that Im trying to get work is

SELECT SUM(a.ProcessHours) FROM
(SELECT Serial, ProcessHours, max(t_stamp) FROM machine_data
WHERE Type = 'BLAST'
group by Serial) AS a;

But its not bringing back the latest date in the query for each serial.

I tried to change it as the latest Hrs will always be the highest hours but this throws a error..

SELECT SUM(a.ProcessHours) FROM
(SELECT Serial, MAX(ProcessHours) FROM machine_data
WHERE Type = 'BLAST'
group by Serial) AS a;

2

Would an inner join work? Something like:

SELECT ID, Serial, ProcessHours, t_stamp
FROM machine_data md
INNER JOIN
    (SELECT Serial, MAX(t_stamp) AS MaxTStamp
    FROM machine_data
    GROUP BY home) groupedmd
ON md.home = groupedmd.home 
AND md.datetime = groupedmd.MaxTStamp
WHERE Type = 'BLAST'

I’m not saying this is it, exactly, without some data to query against, but it may give you some ideas.

3

The grouping is possibly not valid. In the SQL standard, when grouping, every column/expression in the select list must be either in the Group By clause, or have an aggregate function. Most DBs enforce this. MySQL lets you do it, and it “just works” if the one grouping key is unique for the unspecified columns, but otherwise yields pseudo-random results. You aren’t grouping by ProcessHours in your inner query.

4

Thanks Gents,

With your advice and some googling it got me on the right track.

select sum(ProcessHours)
from (SELECT 
    MAX(ProcessHours) as ProcessHours
    FROM `machine_data` 
    where type = 'BLAST'
    group by serial) a

I record the hours run of all the machines of a certain category when they post their info via MQTT. The trouble is there is many records as they post each hour so I only wanted to pick the newest one or in the case I chose the highest value as effectively its the same. :+1:

2 Likes