Help with this sql query

Question

Help with this sql query

Navigation

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

1

I have a question with the following exercise:

Aware of the extra expenses that employees must face in the month of March, it is desired to reward the employee or employees with the highest sales amount for the month of March. To do this, we want to know the name, surname and total amount of sales made in March of the employees who meet this condition. The information must be shown in the format of the example and ordered in ascending order by salary and last name:

This is the query I have, but I can not get the result of the image above.

SELECT
     E.PNOMBRE "NOMBRE",
     E.APPATERNO "APELLIDO",
    SUM( V.MONTO_TOTAL) "MONTO TOTAL VENTAS"
FROM EMPLEADO E JOIN VENTAS V ON(E.ID_EMPLEADO = V.ID_EMPLEADO)
WHERE(FECHA_BOLETA >='01032014' AND FECHA_BOLETA <='31032014')
GROUP BY E.PNOMBRE, E.APPATERNO;

This is my result:

I appreciate your help because I have tried everything but I can not limit it to only showing me the biggest sale since I still do not understand the use of having .

sql query sqldeveloper

asked by Jordan Blake Told 06.04.2017 в 00:38

source

3 answers

1

You still need to put the order by

SELECT
    E.PNOMBRE "NOMBRE",
    E.APPATERNO "APELLIDO",
    SUM( V.MONTO_TOTAL) "MONTO TOTAL VENTAS"
FROM
    EMPLEADO E JOIN VENTAS V ON(E.ID_EMPLEADO = V.ID_EMPLEADO)
WHERE
    (FECHA_BOLETA >='01032014' AND FECHA_BOLETA <='31032014')
GROUP BY
    E.PNOMBRE, E.APPATERNO
ORDER BY
    'MONTO TOTAL VENTAS';

answered by 06.04.2017 в 00:46

1

Many thanks to all friends, take all your examples and get the query this way:

SELECT
    E.PNOMBRE "NOMBRE",
    E.APPATERNO "APELLIDO",
    SUM(V.MONTO_TOTAL) "MONTO TOTAL VENTAS"
FROM
    EMPLEADO E JOIN VENTAS V ON(E.ID_EMPLEADO = V.ID_EMPLEADO)
    WHERE(FECHA_BOLETA BETWEEN '01032014' AND '31032014')
GROUP BY
    E.PNOMBRE, E.APPATERNO
    HAVING MAX(MONTO_TOTAL) = (SELECT MAX(SUM(MONTO_TOTAL)) FROM VENTAS GROUP BY >MONTO_TOTAL);

answered by 06.04.2017 в 20:39

Angular-Cli clear cache How to Overload Operators

score 1 · Accepted Answer

I would do so by completing what you have put @ShinRaeJae_sunbae:

SELECT
   E.PNOMBRE "NOMBRE",
   E.APPATERNO "APELLIDO",
   SUM( V.MONTO_TOTAL) "MONTO TOTAL VENTAS"
FROM
   EMPLEADO E JOIN VENTAS V ON(E.ID_EMPLEADO = V.ID_EMPLEADO)
WHERE
   (FECHA_BOLETA >='01032014' AND FECHA_BOLETA <='31032014')
   AND "MONTO TOTAL VENTAS" = (SELECT TOP 1 SUM( V.MONTO_TOTAL) "MONTO TOTAL VENTAS"
   FROM
      EMPLEADO E JOIN VENTAS V ON(E.ID_EMPLEADO = V.ID_EMPLEADO))
   GROUP BY
      E.PNOMBRE, E.APPATERNO
   ORDER BY
     V.MONTO_TOTAL;)

In this way what you do is to obtain in the subquery the maximum value of the total sum of the sales, and then in the main query you check the employees that have had that total sales in case there are more than one. I do not know if I put quotations of more or less but that would be the idea. I hope it serves you.