How to get the sum of values ​​from a column and multiply by the number that is entered from the keyboard?

Question

Java

There is a database of employees in one table. Connection via Java, JavaFx. There are four columns: id, name, salary , percent. From the keyboard, you need to enter a number that will be multiplied by salary, then multiplied by percent and the value for each employee is displayed on the screen. Further, all the obtained values ​​are summarized and displayed in a separate form. I can't figure out how to put it all together. Perhaps it is worth adding one more calculated column first, so that the sum of salary * percent is placed in it first and then multiplied by the value entered from the keyboard? Well and in the end to make separate request for summation? I'm starting to learn this topic and I will be very grateful for the advice

Answer 1

No calculated columns are needed, everything can be done within the query. I don’t know JAVA, I’ll give it in PHP, but I think the essence will be clear. The request will be something like this:

"SELECT name, salary * percent * ".(int)$_POST['val']." AS value FROM table WHERE 1=1"

Summation, I think, is better done through JAVA: the fewer requests, the better. But you can also make a separate request:

"SELECT SUM(salary * percent * ".(int)$_POST['val'].") AS sum FROM table WHERE 1=1"

Answer 2

You can pull everything out with one request:

SELECT 
  COALESCE(name, 'Total') AS name,
  SUM(salary * percent * 3) AS result 
FROM salaries
GROUP BY name
WITH ROLLUP;

SQL fiddle
here

<?php
$query = "SELECT 
    COALESCE(name, 'Total') AS name,
    SUM(salary * percent * :multiplayer) AS result 
  FROM salaries
  GROUP BY name
  WITH ROLLUP;";
$stmt = $pdo->prepare($query);
$stmt->execute(['multiplayer' => 3]);

$result = $stmt->fetchALL(PDO::FETCH_ASSOC);

var_export($result);

PHP fiddle