From: Peter Brawley
Date: March 14 2006 4:17pm
Subject: Re: Complex Query
List-Archive: http://lists.mysql.com/mysql/195795
Message-Id: <4416EC9F.4050203@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

ElkinFernando Ortiz wrote:

How i calculate for union the other 14
register in the same Query?

...

SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where
e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.H

By having an hours table (hour tinyint) which you join to your energy table:

SELECT h.hour, e.Plant,e.Date,e.Hour,SUM(e.Consuption)
FROM hours h
LEFT JOIN energy e USING (hour)
Where e.Plant=Var_Plant AND e.Date=Var_Date
GROUP BY e.Plant,e.Date,e.H

PB

-----


> I will explain my problem in spanish and english.
>
> Se deben Presentar los consumos por 24 horas de energia para su ingreso. Si
> el usuario ha digitado 10 registros, se deben presentar estos mas los otros
> 14 correspondientes desde la hora 11 hasta la 24 con los consumos en cero.
> Siempre un conjunto de 24 Registros.
> La primera parte de la union es clara, pero como calculo los restantes 14
> registros en la misma consulta ?
>
>
> I need to present consumptions for 24 hours that correspond to energy
> consumptions. If user digit 10 registers, I need to adition others 14
> registers with zero consumptions that correspond from hour 11 to 24. Always
> 24 registers.
> the first part of union is rigth. How i calculate for union the other 14
> register in the same Query?
>
>
> First Part.
> SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where
> e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.Hour
>
> UNION () ??????
>
> Thanks,
>
>
> Elkin
> Medellin,Colombia
>
>
>