Conditional on SUM (mysql)

Question

Conditional on SUM (mysql)

Navigation

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

2

I have the table factura_habitaciones , where you enter the room reservations that I will later invoice.

I want to calculate the total of all the records in the table with a single sentence in MySQL considering that

the days multiply by the price of the room

The cost of the additional guest is 50000 after 5 guests (that is, from numero_huespedes > 4 , since the 4 first ones do not cost)

The total is point 1) added to point 2)

How do I calculate the huesped #5 (>4) with the following sentence:

SELECT SUM( precio_habitacion * dias ) + SUM( numero_huespedes *50000 )
FROM facturacion

pd: the where numero_huespedes > 4 at the end does not work since it would not calculate the precio_habitacion * dias when the condition is not fulfilled

mysql phpmyadmin

asked by SabanaSabrosa 28.10.2018 в 13:32

source

2 answers

2

Try your query as follows

SELECT id, SUM(precio_habitacion * dias) + SUM(numero_huespedes * 50000) AS Costo
FROM hotel
WHERE numero_huespedes > 4
GROUP BY id;

In the end the only thing I added was to group the results by id and in the where I indicate you to do the multiplications and sums only where the number of guests is >4

I'm using the GROUP BY at the end of my query because in the first row I'm using two grouping functions that are SUM()

EXPECTED RESULT

link to the functional example

answered by 28.10.2018 в 15:26

Kivy: Display a text saved in a variable Validation field input text - You must only accept numbers - Javascript

score 3 · Accepted Answer

It's easy. Using IF, THEN, ELSE. See documentation:

Your query would be as follows, it would be:

SELECT SUM( precio_habitacion * dias ) + SUM( numero_huespedes *50000 )
FROM facturacion

To be:

IF numero_huespedes>4 
THEN SELECT SUM( precio_habitacion * dias ) + SUM( numero_huespedes *50000) as Ingreso FROM facturacion
ELSE SELECT SUM( precio_habitacion * dias ) + SUM( numero_huespedes *precio_normal) as Ingreso FROM facturacion
END IF;