Ver Mensaje Individual
  #1 (permalink)  
Antiguo 29/04/2013, 04:29
procam
 
Fecha de Ingreso: abril-2013
Mensajes: 4
Antigüedad: 11 años
Puntos: 0
No consigo hacer esta consulta

Quiero sacar el id_imeter y el número de registros que se han contabilizado en una hora. Sin repetir los ids de los imeters.

Es decir:

| 11342037 | 4 |
| 11342040 | 4 |

Tengo la siguiente tabla:

+-----+----------+------------+----------+---------+--------+-----------+
| id | HLectura | fecha | ILectura | consumo | caudal | id_imeter |
+-----+----------+------------+----------+---------+--------+-----------+
| 1 | 23:00:00 | 2012-11-18 | 1639.643 | 0 | 0 | 11342037 |
| 2 | 22:45:00 | 2012-11-18 | 1639.638 | 0 | 0 | 11342037 |
| 3 | 22:30:00 | 2012-11-18 | 1639.633 | 0 | 0 | 11342037 |
| 4 | 22:15:00 | 2012-11-18 | 1639.628 | 0 | 0 | 11342037 |
| 5 | 22:00:00 | 2012-11-18 | 1639.623 | 0 | 0 | 11342037 |
| 6 | 21:45:00 | 2012-11-18 | 1639.618 | 0 | 0 | 11342037 |
| 7 | 21:30:00 | 2012-11-18 | 1639.613 | 0 | 0 | 11342037 |
| 8 | 21:15:00 | 2012-11-18 | 1639.608 | 0 | 0 | 11342037 |
| 9 | 21:00:00 | 2012-11-18 | 1639.603 | 0 | 0 | 11342037 |
| 10 | 20:45:00 | 2012-11-18 | 1639.598 | 0 | 0 | 11342037 |
| 11 | 20:30:00 | 2012-11-18 | 1638.151 | 0 | 0 | 11342037 |
| 12 | 20:15:00 | 2012-11-18 | 1638.147 | 0 | 0 | 11342037 |
| 13 | 20:00:00 | 2012-11-18 | 1638.143 | 0 | 0 | 11342037 |
| 94 | 23:00:00 | 2012-11-18 | 69.732 | 0 | 0 | 11342040 |
| 95 | 22:45:00 | 2012-11-18 | 69.728 | 0 | 0 | 11342040 |
| 96 | 22:30:00 | 2012-11-18 | 69.726 | 0 | 0 | 11342040 |
| 97 | 22:15:00 | 2012-11-18 | 69.725 | 0 | 0 | 11342040 |
| 98 | 22:00:00 | 2012-11-18 | 69.725 | 0 | 0 | 11342040 |
| 99 | 21:45:00 | 2012-11-18 | 69.724 | 0 | 0 | 11342040 |
| 100 | 21:30:00 | 2012-11-18 | 69.722 | 0 | 0 | 11342040 |
| 101 | 21:15:00 | 2012-11-18 | 69.717 | 0 | 0 | 11342040 |
| 102 | 21:00:00 | 2012-11-18 | 69.717 | 0 | 0 | 11342040 |
| 103 | 20:45:00 | 2012-11-18 | 69.717 | 0 | 0 | 11342040 |
| 104 | 20:30:00 | 2012-11-18 | 69.716 | 0 | 0 | 11342040 |
| 105 | 20:15:00 | 2012-11-18 | 69.716 | 0 | 0 | 11342040 |

Ejecuto esta consulta:

select id_imeter, count(id_imeter) from datos GROUP BY fecha,HLectura;

Y solo me muestra los datos de un solo id_imeter y además no logro que solo salga una unica fila para cada id_imeter.

La salida de la consulta anterior es:

+-----------+------------------+
| id_imeter | count(id_imeter) |
+-----------+------------------+
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |
| 11342037 | 4 |

Espero haberme explicado bien y gracias anticipadas a todos.