CODEWITHSUNDEEP
mysqlsqlphpmyadmin
user3270211
user3270211

Reputation: 933

SQl query showing opposite values

Working on a sql query to show T_Temperatur and T_Badende_per_Time and the values. My sql query changes the comma to dot for the values with Cast, that is not the problem. The problem is that my sql query chose the values of the oppositite title, as you can see here:

SQL:

SELECT routines.date, routines.time, 
SUM( IF( measurements.title =  'T_Temperatur', CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS T_Temperatur, 

SUM( IF( measurements.title =  'T_Badende_per_Time', CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS Badende,

SUM( IF( measurements.title =  'T_Luft_temperatur', CAST( REPLACE( routines.value,  ',',  '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS Luft_Temp

FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
GROUP BY routines.date, routines.time
ORDER BY routines.date, routines.time;

enter image description here

My database is build up with

routines:

enter image description here

measure_routine:

enter image description here

measurements:

enter image description here

Upvotes: 1

Views: 302

Answers (1)

Roland Jansen
Roland Jansen

Reputation: 2783

Swap the parameters in the IF() function, you return 0 if the condition is true!

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3.

SELECT routines.date, routines.time, 
   SUM(IF(measurements.title='T_Temperatur', CAST(REPLACE(routines.value,',','.' ) AS DECIMAL(18,2)), 0)) AS T_Temperatur, 
   SUM(IF(measurements.title='T_Badende_per_Time', CAST(REPLACE(routines.value,',','.') AS DECIMAL(18,2)), 0)) AS Badende
FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
order by routines.date, routines.time

Upvotes: 3

Related Questions