Is this how to use SUM()

Databases
1

I have 2 tables, 1 which holds “total_power” and another which holds “power”

I set it up so that the table with “total_power” simple adds up the “power” for any device connected to that table.
So any foreign key connected to the primary key should tell you what to add.

create table racks (
rack_id INT autonumber,
...
PRIMARY KEY (rack_id)
)
create table devices (
rack_id INT,
...
FOREIGN KEY (rack_id)
)

So now, can I

<?php
include "conn.php";

$rack_id = $_GET{'rack_id'];

$sql = 'SELECT power_requirement FROM devices WHERE rack_id = '.$rack_id;

$result = mysqli_query($conn, $sql);

         if (mysqli_num_rows($result) > 0) {
            while($row = mysqli_fetch_assoc($result)) {
				
                  $sql = ' UPDATE racks SET total_power_requirement = SUM('.$row['power_requiremnt'].')';
   
				   if (mysqli_query($conn, $sql)) {
					  echo "Record updated successfully";
				   } else {
					  echo "Error updating record: " . mysqli_error($conn);
				   }
            }
         } else {	
            echo "0 results";
         }


   mysqli_close($conn);
?>
2

A better approach is to tell us about the real problem you are trying to solve instead of asking about your attempted approach to solving the real problem. Generally, totals are not stored, they are calculated on the fly as needed. I would need info on what you actually have going on to give you the best answers.

3

no

:avocado:

4

that the table with “total_power” simple adds up the “power” for any device connected to that table

Don’t do this. If you need some data calculations, make it directly in application.

5

@lurtnowski this kinda hurt. @igor_g is right. The application should do the calculation for you

6

And by the way, if you want to make something injectable, this is the way.

https://www.php.net/manual/en/function.mysql-real-escape-string.php

7

mysql_real_escape_string is a) deprecated and b) not foolproof.

Instead, one should use prepared statements.

8

Fixed it for you.

1 Like
9

ok, so heres whats going on, I have a devices table, one of its attributes is “power_required”.
I have another table (racks) which can have many devices on it.
I thought id have to create a “total_power” attribute on the racks table to add up the “power_required” for all of its devices, but it would be nice if I can simply do away with the “total_power” attribute and simply do the calculation on the fly. heres the SQL statement to display all the racks

SELECT location, total_power_requirement, elevation, title, power_panel, circuit_breaker, number_of_slots, display, rack_id FROM racks WHERE rack_id > 0

since each device would have a rack_id, how would I alter the SQL so that I can grab the sum of the “power_required” for each rack?

10

SELECT rack_id, SUM(power_required) FROM devices GROUP BY rack_id

EDIT: You probably want to know what rack it belongs to, too.

11

Or to use your full query:

SELECT location, total_power_requirement, elevation, title, power_panel, circuit_breaker, number_of_slots, display, rack_id 
FROM racks 
LEFT JOIN 
(SELECT rack_id, SUM(power_required) AS total_power_requirement FROM devices GROUP BY rack_id) 
AS tpr 
ON tpr.rack_id = racks.rack_id
WHERE rack_id > 0
12

thanks, its ok if I do that as I have like 500 racks (it wont be too much?)
Thanks…

13

I mean, if you’re running your SQL database off of a potato, then 5 racks will challenge it.

Will it take MORE processing power to calculate this on the fly? Yes.
Will it kill any even semi-respectable server? No.

14

ok, ran into an error when I ran it in PHPMyAdmin

Error

SELECT location, elevation, title, power_panel, circuit_breaker, number_of_slots, enabled, rack_id FROM racks LEFT JOIN (SELECT rack_id, SUM(power_requirement) AS total_power_requirement FROM devices GROUP BY rack_id) AS tpr ON tpr.rack_id = racks.rack_id WHERE rack_id > 0 AND enabled = 1 LIMIT 0, 25

MySQL said:

#1052 - Column 'rack_id' in field list is ambiguous

I changed the display attribute to enabled (is that ok?

15

sorry, yes. With the field in both tables, rack_id becomes ambiguous even though it’s being used as the unifying field. So you’d have to declare rack_id as racks.rack_id in the SELECT and WHERE identifiers.

16

yes thanks,

SELECT location
   , elevation
   , title
   , power_panel
   , circuit_breaker
   , number_of_slots
   , enabled
   , racks.rack_id 
FROM racks 
LEFT JOIN 
(SELECT  rack_id
   , SUM (power_requirement) AS total_power_requirement 
   FROM devices GROUP BY rack_id) AS tpr 
ON tpr.rack_id = racks.rack_id 
WHERE racks.rack_id > 0 
AND enabled = 1

worked

17

Im getting
Notice : Undefined index: total_power_requirement in C:\Users\lurtnowski\webserver\htdocs\ICE-v-4.6 -php\san-diego\racks.php on line 140

Which is strange cause isn’t that the aliases name?

$row['total_power_requirement']
18

You didnt select anything called “total_power_requirement”, so that does not exist in your output array.

1 Like
19

Yes and no. You can also calculate sum in one single step in a query using a CTE.

WITH calc as (SELECT sum(col) AS sum FROM table1)
UPDATE table2 SET col=(SELECT sum FROM calc)
20

No. There is not the question of query optimizing. You should to avoid a data redundancy. If not, potentially that will bring a lot of problems.