Count data from a table to sum

Asked

Viewed 50 times

0

Hello folks I’m crando a Dashboard and I came across a challenge and I don’t know what to do.

Table Order of Services 

Nome_do_profissional = Susana  
Serviço = MegaHair   
Valor = 80

I need to create a query that counts how many times the name repeats in the column Nome-do-profissional, and multiply by the amount she would receive as payment, example:

$quatidade = 5;
$valorTipoDeServiço = 80;
$pagamento =  5 * 80;

detail, this value to be multiplied is not the same recorded in the table it will be in another table:

Table TYPE Services 

Serviços = Mega Hair  
Valor = 80
php mysqli phpmyadmin

3 answers

0

You tried some query already?

Test this example :

SELECT COUNT(Nome_do_profissional), valor
FROM Servicos
WHERE Nome_do_profissional = 'Susana';

  • yes I used exactly this logic, I think my problem is in saving the query result in a string, and multiply it by the record value of a second table.

  • as you are saving in variable the result today, edit your post and put this information so that it can help you!

0


Hello, Natan. For this, you will need to use relationship between tables and SQL data grouping.

Suppose we have the table ordens with the following structure:

`ordens`
nome_do_profissional  |   servico
Susana                |   MegaHair
Joana                 |   MegaHair

And the table servicos with:

`servicos`
nome             |   valor
MegaHair         |   80

See that servico and nome_do_servico will be our keys to relate the data between one table and the other. Thus, using the functions GROUP BY and a LEFT JOIN, we can do:

SELECT
    nome_do_profissional,
    SUM(servicos.valor) as valor_total
FROM
    ordens
LEFT JOIN
    servicos
ON
    ordens.servico = servicos.nome
GROUP BY
    ordens.nome_do_profissional

This will return us data where we will have professional name in a column and the total value will accumulate the total value of all services performed by this professional.

See working on Sqlfiddle: http://sqlfiddle.com/#! 9/c3fc4b/22/0

  • Great friend. but how do I print this in my table html. because in my html I would have: <table> <! -- top line --> <td> <td>Name</td> <td>Mega Hair</td> <td>Cut</td> <td>Receive value</td> </tr> <? php while ($dados = mysqli_fetch_assoc($query)) { # code... ? > <! -- Infernal line --> <tr> <td><? php echo $data['Professional'name']; ? ></td> <td><? php echo>

  • Now I’m not getting the problem right. Do you need to take the total amount to be received by the employee or is it something else? This query I showed you will fetch the total amount to be received by the employee.

  • You are so right! I need to pick up how much the employee will receive, and print in my html, if possible in a TABLE excuse I’m a beginner in PHP

  • Do as you are in this gist and it should work: https://gist.github.com/jpedroh/ebc66b9dae065b3fdf679619a0015450

0

TRIES TO ADAPT

DECLARE @nome  nvarchar(MAX);
DECLARE @valor nvarchar(MAX);
DECLARE @query nvarchar(MAX);

SELECT 
    @nome = (SELECT COUNT('NOME') AS nome,
    @valor = (SELECT SUM('VALOR') AS valor
FROM consultas;    

SET @query = '
    SELECT *
    FROM {nome} as a  
    WHERE a.{valor} = @valor
';

An example using SQL, the logic is to save the results of the two selects in two variables and then mount a query with these outworking.

Browser other questions tagged php mysqli phpmyadmin

You are not signed in. Login or sign up in order to post.