Uitkomst mysql query tegen verwachting (avg, min, max)

[.SnifraM]

Wie o wie kan mij helpen met de volgende mysql query.

Ik heb een tabel kwh_log met de volgende structuur:

code:

id,event (timestamp), puls, watt, meterstand
737;"2014-09-13 20:00:43";"21270501";"4234.22";"44313.54"
738;"2014-09-13 20:00:55";"21270502";"602.56";"44313.55"
739;"2014-09-13 20:01:08";"21270503";"602.02";"44313.55"
740;"2014-09-13 20:01:16";"21270504";"895.06";"44313.55"
741;"2014-09-13 20:01:19";"21270505";"2345.57";"44313.55"
742;"2014-09-13 20:01:23";"21270506";"2345.57";"44313.55"
743;"2014-09-13 20:01:26";"21270507";"2344.11";"44313.56"
744;"2014-09-13 20:01:29";"21270508";"2348.51";"44313.56"
745;"2014-09-13 20:01:32";"21270509";"2351.46";"44313.56"
746;"2014-09-13 20:01:35";"21270510";"2351.46";"44313.56"
747;"2014-09-13 20:01:39";"21270511";"2350.72";"44313.57"
748;"2014-09-13 20:01:42";"21270512";"2352.20";"44313.57"
749;"2014-09-13 20:01:45";"21270513";"2351.46";"44313.57"
750;"2014-09-13 20:01:48";"21270514";"2350.72";"44313.57"

Nu wil ik samengevat per uur de laagst gemeten Watt waarde, hoogste Watt waarde en het gemiddelde.

Ik dacht dat het met de volgende query wel zou lukken maar ik kom er niet uit.:

code:

SELECT 
    HOUR(`event`) AS `uur`, 
    ROUND(MIN(`watt`)) AS `minwatt`, 
    ROUND(MAX(`watt`)) AS `maxwatt`, 
    ROUND(AVG(`watt`)) AS `avgwatt` 
FROM `kwh_log` 
WHERE DATE(`event`) = '2014-09-15' 
GROUP by `uur`

Uitkomst:

code:

uur,minwatt,maxwatt,avgwatt
0;"158";"4857";"474"
1;"153";"4442";"525"
2;"148";"587";"916"
3;"100";"98";"505"
4;"143";"4782";"545"
5;"157";"97";"836"
6;"2422";"89";"718"
7;"139";"972";"648"
8;"1289";"993";"909"
9;"130";"96";"588"
10;"106";"5150";"390"
11;"132";"4075";"589"
12;"1051";"77";"988"
13;"145";"77";"538"
14;"133";"77";"343"
15;"133";"3780";"245"
16;"152";"78";"1015"
17;"143";"77";"453"
18;"131";"6333";"310"
19;"130";"83";"486"
20;"136";"89";"789"
21;"316";"599";"396"
22;"316";"364";"322"

Ik krijg nu netjes een overzicht met per uur de gevraagde info. Het gekke is alleen dat de minimum waarde in sommige gevallen veel hoger is dan de maximum waarde. Dat kan natuurlijk niet dus waarschijnlijk is de query niet goed.

Wat doe ik verkeerd? Iemand die me op weg kan helpen?

Alvast bedankt

[Anoniem: 566595]

Moet je niet de quotes om uur weghalen in je group by? Group je zo niet op de string uur?

[ Voor 7% gewijzigd door Anoniem: 566595 op 15-09-2014 23:00 ]

[.SnifraM]

Uitkomst blijft hetzelfde zonder de quotes.

phpMyAdmin zet die ` ` er zelf omheen dus ik hou dat vaak gewoon aan.
Uitkomst blijft overigens hetzelfde als ik ze weghaal. Maakt dus geen verschil.

code:

SELECT 
    HOUR(event) AS uur, 
    ROUND(MIN(watt)) AS minwatt, 
    ROUND(MAX(watt)) AS maxwatt, 
    ROUND(AVG(watt)) AS avgwatt 
FROM kwh_log 
WHERE DATE(event) = '2014-09-15' 
GROUP by uur

[P.O. Box]

je getallen zijn string ipv ints/decimals... dus min en max wordt met een string-compare vergeleken... (denk ik)

ff CAST-en dus

[ Voor 11% gewijzigd door P.O. Box op 16-09-2014 00:35 ]

[gekkie]

definitie van je table (en dus je column types) zou wel handig zijn

[ Voor 11% gewijzigd door gekkie op 16-09-2014 00:38 ]

[RobIII]

Anoniem: 566595 schreef op maandag 15 september 2014 @ 22:56:
Moet je niet de quotes om uur weghalen in je group by? Group je zo niet op de string uur?

Nee; de zgn. backticks (`) ofwel identifier quote character gebruik je om veldnamen te escapen (bijvoorbeeld een veld foo bar werkt niet (en wil je ook niet, maar dat is een ander verhaal...) maar `foo bar` werkt wel als veldnaam in een query). MSSQL gebruikt daar \[ en ] voor. Het is een goed gebruik die dingen om elk veld te zetten, maar de meeste mensen gebruiken 't alleen om bepaalde reserved words bijvoorbeeld te 'escapen'. Waar jij op doelt zijn zgn. single quotes ('), let op de verschillen: ` en '. Die gebruik je om aan te geven dat iets een string/text is.

P.O. Box schreef op dinsdag 16 september 2014 @ 00:33:
je getallen zijn string ipv ints/decimals... dus min en max wordt met een string-compare vergeleken... (denk ik)

Daar zet ik m'n geld ook op in...

P.O. Box schreef op dinsdag 16 september 2014 @ 00:33:
ff CAST-en dus

Liever gewoon zorgen dat de velden van 't juiste type zijn (en dus niet "ff CAST-en") maar gewoon je tabeldefinitie fixen.

gekkie schreef op dinsdag 16 september 2014 @ 00:38:
definitie van je table (en dus je column types) zou wel handig zijn

Psies; de 'CREATE statement' (ofwel de DDL) van de kwh_log tabel zou wel handig zijn. Wel kan ik alvast verklappen dat als (bijna) alle velden van 't type (var)char zijn je daar dus eerst eens naar moet gaan kijken en voor élk van de velden in de tabel de juiste types kiest.

[ Voor 40% gewijzigd door RobIII op 16-09-2014 01:10 ]

[.SnifraM]

De betreffende velden had ik inderdaad op varchar gezet. Ik heb ze nu veranderd en het werkt !!

code:

id  int(11)
event   timestamp
puls    int(20)
watt    int(10)         
kwh decimal(10,2)

Bedankt voor jullie hulp

[ Voor 15% gewijzigd door .SnifraM op 16-09-2014 07:58 ]

[P.O. Box]

RobIII schreef op dinsdag 16 september 2014 @ 01:00:
[...]

Liever gewoon zorgen dat de velden van 't juiste type zijn (en dus niet "ff CAST-en") maar gewoon je tabeldefinitie fixen.

helemaal mee eens, maar kreeg gezien de aard van de data het idee dat de data zo werd aangeleverd en daar niets in te veranderen was... verkeerde aanname van mij

[mrwiggs]

marviins schreef op dinsdag 16 september 2014 @ 07:58:
De betreffende velden had ik inderdaad op varchar gezet. Ik heb ze nu veranderd en het werkt !!

code:

id  int(11)
event   timestamp
puls    int(20)
watt    int(10)         
kwh decimal(10,2)

Bedankt voor jullie hulp

Had 'watt' niet ook een decimal moeten zijn? Al zal dat nu te laat zijn voor de data die er al in staat...

[ Voor 7% gewijzigd door mrwiggs op 16-09-2014 09:56 ]

[.SnifraM]

De waarde van 'Watt' krijg ik momenteel vanuit mijn Arduino script en die is daar al afgerond tot een rond getal, vandaar dat ik hem op int zet.

Wellicht nog een idee om het getal niet af te ronden op de Arduino, maar als decimal op te slaan. Zo wordt de berekening nauwkeuriger. Thanks!

[The Eagle]

Integers hebben vziw een vliegende komma dus dat gaat wel goed. Een decimal had idd wel mooier geweest.

Nvm, ik was nog niet goed wakker geloof ik.
Werd ook getypt vanaf een Grieks terrasje in de zon met koffie, verse jus en Griekse yoghurt met honing, kortom: ontbijt

@hierinder: thx

[ Voor 50% gewijzigd door The Eagle op 16-09-2014 12:36 ]

[Voutloos]

The Eagle schreef op dinsdag 16 september 2014 @ 10:11:
Integers hebben vziw een vliegende komma dus dat gaat wel goed. Een decimal had idd wel mooier geweest.

Ehm nee. Ben bang dat je niet helemaal wakker bent.

[GC-Martijn]

Ik denk dat je in een 'rare' constructie van mysql bent beland.

Daar heb ik ook bij piwik een soort gelijke ticket voor lopen.
http://forum.piwik.org/read.php?3,115060,115060

komt er op neer dat je in de group by alle kolommen moet zetten.
daarom heb ik ook altijd de valid sql mode ONLY_FULL_GROUP_BY aanstaan.

daarna zou het moeten werken.

edit: o ik zie na een refresh dat het al iets anders was haha.
maar misschien heb je er later nog wat aan, in geavanceerde group by's

[ Voor 16% gewijzigd door GC-Martijn op 16-09-2014 10:44 ]

[P.O. Box]

GC-Martijn schreef op dinsdag 16 september 2014 @ 10:41:
I
komt er op neer dat je in de group by alle kolommen moet zetten.
daarom heb ik ook altijd de valid sql mode ONLY_FULL_GROUP_BY aanstaan.

technisch gezien moet dat eigenlijk altijd, v.z.i.w. is MySQL de enige DB die dat niet afdwingt (wat op zich niet erg is als je het weet, maar veel mensen doen hierdoor verkeerde aannames uit de data die ze zien)

[Chrotenise]

GC-Martijn schreef op dinsdag 16 september 2014 @ 10:41:
Ik denk dat je in een 'rare' constructie van mysql bent beland.

Daar heb ik ook bij piwik een soort gelijke ticket voor lopen.
http://forum.piwik.org/read.php?3,115060,115060

komt er op neer dat je in de group by alle kolommen moet zetten.
daarom heb ik ook altijd de valid sql mode ONLY_FULL_GROUP_BY aanstaan.

daarna zou het moeten werken.

edit: o ik zie na een refresh dat het al iets anders was haha.
maar misschien heb je er later nog wat aan, in geavanceerde group by's

Die ticket kan je sluiten. Het niet opnemen van elke select table in je SQL group by is namelijk incorrect gebruik van SQL en wordt daarom I'm ieder geval in mssql niet geaccepteerd.

[RobIII]

Chrotenise schreef op dinsdag 16 september 2014 @ 18:09:
Het niet opnemen van elke select table in je SQL group by is namelijk incorrect gebruik van SQL en wordt daarom I'm ieder geval in mssql niet geaccepteerd.

Je bedoelt "Het niet opnemen van elke non-aggregate in je group by is namelijk incorrect gebruik...". Overigens staat dat ook al sinds jaar-en-dag in onze FAQ

Hoe werkt dat GROUP BY nu eigenlijk?

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

[gekkie]

RobIII schreef op dinsdag 16 september 2014 @ 18:17:
[...]

Je bedoelt "Het niet opnemen van elke non-aggregate in je group by is namelijk incorrect gebruik...". Overigens staat dat ook al sinds jaar-en-dag in onze FAQ

Hoe werkt dat GROUP BY nu eigenlijk?

[...]

Dan toch liever postgresql dat je liefdevol weigert en tegelijkertijd netjes aanraad ze toe te voegen aan je group by
Alles beter dan random data uit een database laten spugen

[RobIII]

gekkie schreef op dinsdag 16 september 2014 @ 18:30:
[...]

Dan toch liever postgresql dat je liefdevol weigert en tegelijkertijd netjes aanraad ze toe te voegen aan je group by
Alles beter dan random data uit een database laten spugen

Daarom werd eerder al ONLY_FULL_GROUP_BY aangehaald Daarmee weigert MySQL die queries ook gewoon. Maar we zullen maar weer eens on-topic gaan, niet?

[ Voor 4% gewijzigd door RobIII op 16-09-2014 18:33 ]

[.SnifraM]

Nooit geweten maar nu ik het gelezen heb zeer zeker nuttige info, thanks!