SQL Group SUM by idemployed

Question

SQL Group SUM by idemployed

Navigation

#1 by (0 votes)

1

good and tried in different ways to try to group a sum of 3 tables I will write the example to be clearer

SELECT
    v.fecha_ven,
    e.apellidopaterno_emp,
    e.nombre_emp,
    SUM(d.precio_dv * d.cantidad_dv) AS Total
FROM
    detalleventa d
INNER JOIN venta v ON
    d.idventa = v.idventa
INNER JOIN empleado e ON
    v.idempleado = e.idempleado
GROUP BY
    e.idempleado
HAVING
    DATE_FORMAT(v.fecha_ven, '%m-%Y') = '12-2018'

and the result would be this

but that result only appears when all the records are one month, when adding more records in another month this happens

As you see, the other Employee disappears to make clear what I did was to change the date 2 records (id 4 and 5) of the other employee already existing a month ago

this causes the total sum of the 3 records to be shown in month 11

and what I'm trying to do is to add the records according to their date

I would appreciate your help thanks: D

mysql select consulta-sql group-by

asked by Luis A. Reymundo Beltran 20.12.2018 в 22:34

source

1 answer

Encrypt data via URL in Visual Basic.net I get an error in "if ($ stmt- execute ())" with PDOStatement :: execute (): SQLSTATE [HY093]: Invalid parameter number

score 0 · Accepted Answer

The employee disappears because you are getting the following result (before HAVING ):

fecha_ven   apellidopaterno     Total
2018-12-25  reymundo            1305
2018-11-15  lopez               119

This is because when grouping only by employee, we have the problem that there is more than one month for the same employee, and the engine chooses the first, which is 11. And then, when applying the clause HAVING DATE_FORMAT(v.fecha_ven, '%m-%Y') = '12-2018' indicate that you only want month 12 so you discard the result of month 11.

We will work with a summarized version of your tables and with data similar to those provided:

create table venta(
    idventa int,
    idempleado int,
    cliente_ven varchar(45),
    fecha_ven date
);

create table empleado(
    idempleado int,
    apellidopaterno varchar(20)
);

create table detalleventa(
    iddetalleventa  int,
    idventa int,
    cantidad_dv    int,
    precio_dv   decimal(15,2)
);


insert into empleado values (1,'reymundo');
insert into empleado values (2,'lopez');

insert into venta values (4,2,'juan', '2018/11/15');
insert into venta values (5,2,'maria', '2018/11/22');
insert into venta values (6,1,'muro', '2018/12/25');
insert into venta values (7,2,'miro', '2018/12/11');
insert into venta values (8,1,'mir', '2018/12/14');
insert into venta values (9,1,'iii', '2018/12/16');
insert into venta values (10,1,'asd', '2018/12/18');

insert into detalleventa values (4, 4, 2, 2.0);
insert into detalleventa values (5, 5, 1, 15.0);
insert into detalleventa values (6, 6, 3, 10.0);
insert into detalleventa values (7, 7, 2, 50.0);
insert into detalleventa values (8, 8, 1, 100.0);
insert into detalleventa values (9, 9, 5, 200.0);
insert into detalleventa values (10, 10, 7, 25.0);

Now, the detail is to group the sales of each employee, by month and by year, since if we only group by month, when there are sales of 2019 or previous years, will add by month regardless of the year. We can use the functions of MONTH and YEAR of MySql:

select idempleado, MONTH(fecha_ven) mes, YEAR(fecha_ven) año
from venta
group by idempleado, mes, año

idempleado  mes     año
1           12     2018
2           11     2018
2           12     2018

And we can see that we have grouped the employees by month and year. Now, we add the total detail of the sales for each month and year of each employee:

select idempleado, MONTH(fecha_ven) mes, YEAR(fecha_ven) año, SUM(d.cantidad_dv * precio_dv) as total
from venta v INNER JOIN detalleventa d
ON v.idventa = d.idventa
group by idempleado, mes, año

The results are:

idempleado  mes     año     total
1           12     2018     1305
2           11     2018     19
2           12     2018     100

We can verify that they are indeed correct. Now add the employee table to get the last name:

select e.apellidopaterno, v.idempleado, MONTH(fecha_ven) mes, YEAR(fecha_ven) año, SUM(d.cantidad_dv * precio_dv) as total
from venta v INNER JOIN detalleventa d
ON v.idventa = d.idventa
INNER JOIN empleado e
ON v.idempleado = e.idempleado
group by v.idempleado, mes, año

And we get the desired result:

apellidopaterno     idempleado  mes     año     total
reymundo                  1     12     2018     1305
lopez                     2     11     2018     19
lopez                     2     12     2018     100

You can review the queries and data in fiddle .