I am making a system in which each project is assigned a user. When the user enters and sees the project that was assigned to him, he must enter the hours worked at the end of the day. I show you the relationship between the tables.
project table
idp | titulo
------------------
02 | Proyecto DD1
resource table (users)
id | nombre
-------------------
01 | Usuario A1
02 | Usuario B1
Hours Table
idh | idpro | idrecurso | horas | fecha
-------------------------------------
01 | 02 | 01 | 4 | 2019-08-20
02 | 02 | 02 | 10 | 2019-08-20
03 | 02 | 02 | 8 | 2019-08-21
04 | 02 | 01 | 6 | 2019-08-21
I do two INNER JOIN
to relate the tables as follows:
<?php
$glist = $conn->query("SELECT * FROM proyectos INNER JOIN horaspro ON horaspro.idpro = proyectos.idp INNER JOIN recurso ON horaspro.idrecurso = recurso.id WHERE idp = '".$idp."' GROUP BY idrecurso");
?>
And the result I get is:
Usuario A1 - 4 hs
Usuario B1 - 10 hs
Usuario B1 - 8 hs
Usuario A1 - 6 hs
When the result would have to be:
Usuario A1 - 10 hs
Usuario B1 - 18 hs
What I tried to do is add a at the end of the query GROUP BY idrecurso
but it doesn't group the repeated records. I also have to take only the numbers, regardless of the user, and add them to obtain a general total of hours, I tried to do it with com SUM
but it doesn't work for me either. I tried to use some examples that I found on the web, but they did not work and it must be that I do not understand them well either. A thousand apologies, but my lack of knowledge, but I can not solve it.
Assuming that table
proyecto
yrecurso
has a primary key called "id", this is how I would do the sql statement:And that gives you the result:
For the result that you expect (to show the total of hs that each user has) you must do a
group by
in combination with the aggregate functionsum()
as Damian Perez did, although it is not necessary to join the project table. And for the clauseidp = '".$idp."'
you use clausewhere
That is to say that complete would be:For the general total of hours, you make another query also using
sum()
but withoutgroup by
it since you do not have to addhoras
by grouping byusuario
, but more simply you have to add allhoras
of the hours in the tablehoraspro
for a certain project, like this: