Good evening friends, I would like to ask for your help since I am blocked in terms of the logic of a query in my DB.
I pose the problem: I need to know how many consumptions a person had in the restaurant, this includes classifying by breakfast, lunch and dinner, I also classify by type of food by Rancho (cold (Ex: yogurt, milk, sandwich) or normal (normal food) ), in this way, you would need to produce a report per month specifying how many breakfasts, lunches and dinners were from Rancho Frío and from Rancho Normal.
Queries:
SELECT empresa.idempresa, empresa.nombre AS empresa, comensal.idcomensal, comensal.dni, comensal.nombres, consumo.create_date AS Fecha,
COUNT(CASE WHEN consumo.tipo_ran LIKE '%normal%' THEN 1 END) AS Normal,
COUNT(CASE WHEN consumo.tipo_ran LIKE '%frio%' THEN 1 END) AS Frio,
COUNT(CASE WHEN consumo.tipo_pla LIKE '%Desayuno%' THEN 1 END) AS Desayunos,
COUNT(CASE WHEN consumo.tipo_pla LIKE '%Almuerzo%' THEN 1 END) AS Almuerzo,
COUNT(CASE WHEN consumo.tipo_pla LIKE '%Cena%' THEN 1 END) AS Cena
FROM `consumo`
LEFT JOIN comensal ON consumo.idcomensal = comensal.idcomensal
LEFT JOIN empresa ON empresa.idempresa = comensal.idempresa
WHERE create_date = '2018-08-14'
GROUP BY comensal.nombres
Which gives me the following result:
This is where I am unable to classify consumption by type of ranch (how many breakfasts, lunches and dinners were from Cold Ranch or Normal Ranch).
My table:
I appreciate any suggestion or comment. Thanks for taking the time to read my question.
It occurs to me that in the comparison of what dish it is, also add the type of Ranch, something like that...
And so you classify each ranch with each type of dish at the same time.