# MYSQL Query Value Different

**URL:** <https://forum.inductiveautomation.com/t/mysql-query-value-different/22386>\
**Category:** General Discussion\
**Created:** [February 6, 2019, 3:47pm UTC](https://forum.inductiveautomation.com/t/mysql-query-value-different/22386 "2019-02-06T15:47:31Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\
**Post date:** [February 6, 2019, 3:47pm UTC](https://forum.inductiveautomation.com/t/mysql-query-value-different/22386/1 "2019-02-06T15:47:31Z")

</div>

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

So for example I perform this query..

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

```

It returns..

```auto
|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;

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [February 6, 2019, 8:22pm UTC](https://forum.inductiveautomation.com/t/mysql-query-value-different/22386/2 "2019-02-06T20:22:36Z")

</div>

Would an inner join work? Something like:

```auto
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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 6, 2019, 11:27pm UTC](https://forum.inductiveautomation.com/t/mysql-query-value-different/22386/3 "2019-02-06T23:27:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\
**Post date:** [February 7, 2019, 9:40am UTC](https://forum.inductiveautomation.com/t/mysql-query-value-different/22386/4 "2019-02-07T09:40:53Z")

</div>

Thanks Gents,

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

```auto
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. 👍