max and min within sum SQL

Question:

I need to show the month with the most sales and the month with the least sales, for this I have the following code:

CREATE TABLE Ventas (
id INT primary key,
idmes INT,
ventas INT);
CREATE TABLE Mes (
idmes INT primary key,
mes VARCHAR(15));

INSERT INTO Ventas VALUES
(1, 7, 5), (2, 6, 5), (3, 5, 10), (4, 3, 6), (5,
7, 10),(8, 6,1), (9,3, 12),(10,9,8), (11, 12, 5), (12, 6,
0),(13, 6, 5),(14, 12, 1), (15, 8, 7), (16, 1, 3), (17, 6,
2),(19, 5,2), (20, 1, 4);
INSERT INTO Mes VALUES
('1','Enero'),('2','Marzo'),('3','Abril'),('4','Febrero'),('5','Mayo'),
('6','Julio'),('7','Septiembre'),('8','junio'),('9','Agosto')
,('10','Octubre'),('11','Noviembre'),('12','Diciembre');

select mes, SUM(ventas) as ventasT 
from (
(select mes, MAX(ventasT) from ventas  
inner join mes on mes.idmes = ventas.idmes
group by mes order by ventasT desc limit 1)
UNION
( select mes, MIN(ventasT) from ventas  
inner join mes on mes.idmes = ventas.idmes
group by mes order by ventasT asc limit 1) )
usuarios;

I mark error saying that the union syntax is wrong from the first line, I await your answer. What I need is to show the month with the most sales which would be July with 18 and the month with the least sales which would be December with 6

Answer:

You have several errors:

  • The columns ventasT and mes do not exist.
  • If you use MAX() you will only get the Ventas record with the most ventas , but you need to add them first to know the total and then be able to apply this function.
  • If you use MIN() you will only get the Ventas record with fewer ventas …

Upgrade:

After an informative and constructive conversation with @gbianchi and after several comments from him, we agreed that a better solution to this problem could be the following query:

Solution:

SELECT
  (SELECT M.mes FROM Mes AS M WHERE M.idmes = T.idmes) AS mes,
  T.ventasT
FROM (
  (SELECT
      idmes,
      SUM(ventas) AS ventasT
    FROM Ventas
    GROUP BY idmes
    ORDER BY ventasT DESC
    LIMIT 1)
  UNION ALL
  (SELECT
      idmes,
      SUM(ventas) AS ventasT
    FROM Ventas
    GROUP BY idmes
    ORDER BY ventasT ASC
    LIMIT 1)
) AS T

Description:

The first query of the UNION adds the ventas of records in the Ventas table, grouping them by idmes and then idmes them by the total in a descending way ( highest first ) and only returning 1.

The second UNION query is almost identical, only the order is changed to ascending ( least first ).

By joining the result of both, we obtain the month with the highest and lowest sales.

Finally in the main query we make a sub-query to obtain the name of the month of these 2 records.

Solution

Taking into account the aforementioned, you could get the expected result like this:

(SELECT
    M.mes,
    SUM(V.ventas) AS ventasT
  FROM Ventas AS V
    INNER JOIN Mes AS M
      ON M.idmes = V.idmes
  GROUP BY V.idmes
  ORDER BY ventasT DESC
  LIMIT 1)
UNION ALL
(SELECT
    M.mes,
    SUM(V.ventas) AS ventasT
  FROM Ventas AS V
    INNER JOIN Mes AS M
      ON M.idmes = V.idmes
  GROUP BY V.idmes
  ORDER BY ventasT ASC
  LIMIT 1)

Demo

Description:

The first query adds the ventas of records in the Ventas table, grouping them by idmes and then idmes them by the total in descending order ( highest first ) and only returning the last one.

The second query is almost identical, only the order is changed to ascending ( least first ).

Post Views: 0
Scroll to Top