Obtain the individual total by registration

Question

Obtain the individual total by registration

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have the following tables:

                    REPORTEBETEITIVA
_______________________________________________________
|  idReporteBeteitiva   |    HorasTrabajo |Trabajador |  
-------------------------------------------------------
|        1              |         8       |     1     |
-------------------------------------------------------
|        2              |         8       |     1     |
-------------------------------------------------------
|        3              |        14       |     2     |
-------------------------------------------------------


                        TRABAJADOR
    ________________________________________
    |  idTrabajador   |  Nombre  |  Cedula |  
    ----------------------------------------
    |        1        |   PEDRO  | 1054632 |
    ----------------------------------------
    |        2        |   CAMILO | 1234555 |
    ----------------------------------------

What I want is that at the time of making the query, I add the total number of hours under each record per user, so:

       ______________________
       |  Nombre |  HorasT  |   
       ----------------------
       |   Pedro |   16     |
       ----------------------
       |  TOTAL  |   16     | 
       ----------------------
       |  Camilo |   12     | 
       ----------------------
       |  TOTAL  |   12     | 
-----------------------------
|TOTAL           |   28     |
-----------------------------

What I want is to show me the individual result, only I have achieved the following:

       ______________________
       |  Nombre |  HorasT  |   
       ----------------------
       |   Pedro |   16     |
       ----------------------
       |  Camilo |   12     | 
-----------------------------
|TOTAL           |   28     |
-----------------------------

My query:

SELECT trabajador.Nombre, sum(HorasTrabajo) as HorasT FROM 
reportebeteitiva INNER JOIN trabajador ON reportebeteitiva.Trabajador = 
trabajador.idTrabajador group by trabajador.Cedula;

Thanks for the help !!!

mysql

asked by CristianLRS1997 06.06.2018 в 17:29

source

3 answers

How to store product names in the Database in variables and then display them in a WindowsForms MessageBox? Re-activate a disabled button C # Windows Forms

score 1 · Answer 1

1

Look, I think that you already have what you need, the problem is in the way of showing it, and that you have to do in the programming language that you are using.

answered by 06.06.2018 в 18:27

score 0 · Answer 2

You have it easy. the only thing you want are the same data twice with different names.

SELECT * FROM (
    SELECT trabajador.Nombre, HorasTrabajo as HorasT FROM 
    reportebeteitiva INNER JOIN trabajador ON reportebeteitiva.Trabajador = 
    trabajador.idTrabajador group by trabajador.Cedula

    UNION

    SELECT trabajador.Nombre+'_TOTAL', sum(HorasTrabajo) as HorasT FROM 
    reportebeteitiva INNER JOIN trabajador ON reportebeteitiva.Trabajador = 
    trabajador.idTrabajador group by trabajador.Cedula

) T order by Nombre  WITH ROLLUP;

Although I think it is not necessary, and you must do it in the view.

score 0 · Answer 3

What I would do would be: with a view obtain the hours of worker 1, and another view with the hours of worker 2 to later add the value obtained from the two views.

Example:

create or replace view t1(horas1) as
SELECT sum(HorasTrabajo) as HorasT FROM 
reportebeteitiva INNER JOIN trabajador ON reportebeteitiva.Trabajador = 
trabajador.idTrabajador where trabajador.idTrabajador=1;

create or replace view t2(horas2) as
SELECT sum(HorasTrabajo) as HorasT FROM 
reportebeteitiva INNER JOIN trabajador ON reportebeteitiva.Trabajador = 
trabajador.idTrabajador where trabajador.idTrabajador=2;

select sum(horas1+horas2) as TOTAL from t1,t2;

Something like that would be my idea, try it and you'll tell us how it has gone.

Greetings.