Question 246176 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Multiply value in SQL script

How can i multiply value in SQL only for in my case specific device (IP)?

My table:

ID DEVICE POWER ENERGY DATE_OF_READING
1 192.168.25.35 165 250241 2023-09-15 08:25:04
2 192.168.25.36 320 190322 2023-09-15 08:25:04

What i want is that sql will return value 650 = 165 x 2 + 320

Currently my script looks like this

SELECT SUM(power) AS "POWER", CONVERT_TZ(date_of_reading,'+2:00','+00:00') AS "DATE OF READING" 
FROM energy.realtime 
WHERE device IN ('192.168.25.35', '192.168.25.36')
Tags:

2

Answers


  1. 0

    To multiply the POWER value by 2 for a specific device (IP) in your SQL query, you can use a CASE statement within the SUM function.

    SELECT 
    SUM(CASE WHEN device = '192.168.25.35' THEN power * 2 ELSE power END) AS "POWER", 
    CONVERT_TZ(date_of_reading,'+2:00','+00:00') AS "DATE OF READING" 
FROM energy.realtime 
WHERE device IN ('192.168.25.35', '192.168.25.36')
    Login or Signup to reply.
  2. 0

    You can also do it with this querie:

    SELECT SUM(IF(device='192.168.25.35',power*2,power)) AS "POWER"
 ,CONVERT_TZ(date_of_reading,'+2:00','+00:00') AS "DATE OF READING" 
FROM energy.realtime 
WHERE device IN ('192.168.25.35', '192.168.25.36');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search