Consulta SQL Imposible?? Son solo 3 campos pero ya no se que hacer

[GsutavoDeLanus]

Hola a todos, este es mi primer mensaje. Me registré acá porque cada vez que buscaba por google alguna pregunta avanzada terminaba llendo a algún treadh de este foro. Se ve que hay gente que sabe por acá! Espero puedan ayudarme


Tengo estas 2 tablas

TABLA 1 (Puntos)
Nombre

TABLA 2 (Compras)
Nombre
Cantidad

La primeras 2 consultas me andan bien

Código PHP:

   Por ejemplo cuantas veces aparece Gustavo, Carlillo, etc 
$sql = "SELECT Nombre,COUNT(*) as Total_puntos FROM Puntos GROUP BY Nombre ORDER BY Total_puntos DESC"; 
 
$sql_compras = "SELECT Nombre,SUM(Cantidad) as Total_cantidad FROM Compras GROUP BY Nombre";  
   

Pero ahora necesito una 3er consulta algo mas complicada

Necesito mostrar los campos:

TABLA 3:
Nombre
Total_puntos
Total_cantidad

Lo que no se hacer es integrar las 2 consultas anteriores en una sola.
Osea, Necesito que la consuta:

Agrupe el Campo Nombre de la TABLA1.
Cuente (COUNT(Tabla1.Nombre)) cuantas veces aparece cada Nombre en Tabla 1 y los guarde (AS) en un campo, por ejemplo Total_Puntos, osea 1 punto por cada vez que estaba el Nombre en la TABLA 1.
A la vez la consulta debe agrupar de la TABLA 2 por el campo Nombre. Pero esta vez sumar para cada nombre el campo Cantidad (SUM(Tabla2.Cantidad)) y guardarlo en un campo nuevo llamado Total_Compras

Osea,tiene que contar en la tabla 1 y sumar en la tabla 2 y alinear por el campo Nombre.

Voy con los ejemplos:

TABLA1 (Puntos)
Nombre
Gustavo
Carlillo
Gustavo
Juancete
Gustavo
Juancete


TABLA 2 (Compras)
Nombre - Cantidad
Gustavo ----- 20
Gustavo ------ 5
Juancete ----- 30
Carlillo -------- 5
Juancete ------ 2

La 3er consulta me debe mostrar esto:

TABLA 3
Nombre - Total Putos - Total Compras
Gustavo ------ 3 ------------ 25
Juancete ----- 2 ------------ 32
Carlillo ------- 1 ------------- 5

Si puede ser ordenado por Total_Puntos, jej espero no pedir demasiado


Saludos, espero que me entiendan.


Muchas gracias!

[pragone]

Hola Gsutavo :D

Pues entiendo tu problema... verás.. no hay una forma sencilla de conseguir lo que quieres pues al hacer un Join entre las tablas esto te multiplica los valores en cualquiera de ellas según la cantidad de registros de la otra.
Pero yo no diría que es una limitación de SQL sino un diseño un poco defectuoso de tu estructura.
Te recomedaría que crearas una tabla para almacenar los nombres y que en ella coloques como un campo el número de puntos.
Incluso si necesitaras tener un registro por cada vez que asignaras puntos a una persona, lo mejor sería que tuvieras esta tabla de nombres y aparte la de la asignación de puntos. En este caso tu estructura quedaría:
Tabla 1: Personas
nombre

Tabla 2: Puntos
nombre
puntos
fecha (quizás)

Tabla 3: Compras
nombre
cantidad

Bajo este esquema, la tabla 1 sólo tendría valores únicos de nombres y la SQL que necesitas es trivial... más aún te recomendaría que le asignaras un ID a cada persona y que lo utilices como clave foránea en las otras dos tablas..
En fin, que sigas un poco mejor un esquema de normalización de BD.

[GsutavoDeLanus]

Iniciado por pragone

Hola Gsutavo :D

Pues entiendo tu problema... verás.. no hay una forma sencilla de conseguir lo que quieres pues al hacer un Join entre las tablas esto te multiplica los valores en cualquiera de ellas según la cantidad de registros de la otra.
Pero yo no diría que es una limitación de SQL sino un diseño un poco defectuoso de tu estructura.
Te recomedaría que crearas una tabla para almacenar los nombres y que en ella coloques como un campo el número de puntos.
Incluso si necesitaras tener un registro por cada vez que asignaras puntos a una persona, lo mejor sería que tuvieras esta tabla de nombres y aparte la de la asignación de puntos. En este caso tu estructura quedaría:
Tabla 1: Personas
nombre

Tabla 2: Puntos
nombre
puntos
fecha (quizás)

Tabla 3: Compras
nombre
cantidad

Bajo este esquema, la tabla 1 sólo tendría valores únicos de nombres y la SQL que necesitas es trivial... más aún te recomendaría que le asignaras un ID a cada persona y que lo utilices como clave foránea en las otras dos tablas..
En fin, que sigas un poco mejor un esquema de normalización de BD.

OK, muchas gracias. Voy a tener que hacer mi PLAN B. Una sola tabla
Supongo que podré sacar una consulta que agrupe por Nombre, Sume Puntos y Sume Compras

TABLA 1
ID
Nombre
Puntos
Compras

Además me quedaría la opción de asignar mas de un punto por cada vez que aparece un Nombre. Pero primero tendré que modificar el código PHP.

Nuevamente Gracias

PD: Jej, si la pifie en mi propio nombre

[TolaWare]

No es muy recomendable armar consultas complejas, por una cuestión de de mantenimiento futuro, es recomendable mantener la complejidad de las mismas en un nivel entendible.

Yo te diría que dejes las 2 consultas por separado.

[GsutavoDeLanus]

Todavía no me termina de convencer lo de una tabla sola.

Otra cosa que se me había ocurrido es guardar los datos de las primeras 2 consultas en 2 vectores y después tratar de mostrar la información de la 3cer consulta(la imposible) recorriendo los vectores desde PHP. Pero tendré que utilizar ciclos anidados, y a medida que crezcan las tablas estaría sobre-utilizando al servidor Apache.


Trataré de implementar la propuesta del usuario pragone.


pragone: Si no es mucho pedir, podrías darme un poco mas de información sobre esa consulta trivial que mencionaste. Mientras voy a leer un poco mas sobre claves foraneas.

Muchas gracias! Saludos!

[pragone]

No hay problema Gustavo.

Primero que nada, te dirijo a la Wikipedia para más info sobre diseño de estructuras:
http://es.wikipedia.org/wiki/Normalizaci%C3%B3n_de_una_base_de_datos (Todavía no puedo colocar enlaces).... aunque es un poco teórico, leería por lo menos los tres pasos de normalización de una BD, lo que es un Modelo de Entidad-Relación y las definiciones como "Clave foránea".

Ahora bien, respecto a tu sistema, me imagino que tiene que ver con algo de compras (no me preguntes cómo... simplemente lo sé ), así que pensaría en la siguiente estructura:
Tabla 1: Cliente
id: integer auto_increment primary key (esta es la clave primaria de esta tabla, asigna un número único al cliente)
nombre: varchar(100)

Tabla 2: Puntos
id_cliente: integer // es el número de identificación del cliente que está ganando los puntos
fecha: timestamp // momento en el que le asignas los puntos
puntos: integer // la cantidad de puntos que le asignas
En esta tabla, la clave primaria sería compuesta (id_cliente y fecha)

Tabla 3: Compras
id: integer auto_increment primary key (esta es la clave primaria de esta tabla, asigna un número único a la compra)
id_cliente: integer // es el número de identificación del cliente que ha realizado la compra
monto: float // monto de la compra
fecha: timestamp // momento en el que se hace la compra.

Con esta estructura, las consultas que necesitas son:
1) Puntos por cliente:
SELECT cliente.nombre, SUM(puntos.puntos) FROM cliente LEFT JOIN puntos ON (cliente.id=puntos.id_cliente) GROUP BY cliente.nombre;
Beneficios de esta query con la que tenías:
- También te muestra los clientes que no tienen puntos
- Si quieres puedes agregar un WHERE indicando un rango de fechas para saber qué cliente ganó puntos en un período de tiempo específico

2) Compras por cliente: (Más de los mismo)
SELECT cliente.nombre, SUM(compra.monto) FROM cliente LEFT JOIN compra ON (cliente.id=compra.id_cliente) GROUP BY cliente.nombre
Beneficios, los mismos

3) Puntos y compras por cliente:
SELECT cliente.nombre, SUM(puntos.puntos), SUM(compra.monto) FROM cliente LEFT JOIN puntos ON (cliente.id=puntos.id_cliente) LEFT JOIN compra ON (cliente.id=compra.id_cliente) GROUP BY cliente.nombre;

Y listo. Verás que mientras mejor diseñada está la estructura más fácil es obtener la información que deseas.

[GsutavoDeLanus]

Iniciado por pragone

No hay problema Gustavo.

Primero que nada, te dirijo a la Wikipedia para más info sobre diseño de estructuras:
http://es.wikipedia.org/wiki/Normalizaci%C3%B3n_de_una_base_de_datos (Todavía no puedo colocar enlaces).... aunque es un poco teórico, leería por lo menos los tres pasos de normalización de una BD, lo que es un Modelo de Entidad-Relación y las definiciones como "Clave foránea".

Ahora bien, respecto a tu sistema, me imagino que tiene que ver con algo de compras (no me preguntes cómo... simplemente lo sé ), así que pensaría en la siguiente estructura:
Tabla 1: Cliente
id: integer auto_increment primary key (esta es la clave primaria de esta tabla, asigna un número único al cliente)
nombre: varchar(100)

Tabla 2: Puntos
id_cliente: integer // es el número de identificación del cliente que está ganando los puntos
fecha: timestamp // momento en el que le asignas los puntos
puntos: integer // la cantidad de puntos que le asignas
En esta tabla, la clave primaria sería compuesta (id_cliente y fecha)

Tabla 3: Compras
id: integer auto_increment primary key (esta es la clave primaria de esta tabla, asigna un número único a la compra)
id_cliente: integer // es el número de identificación del cliente que ha realizado la compra
monto: float // monto de la compra
fecha: timestamp // momento en el que se hace la compra.

Con esta estructura, las consultas que necesitas son:
1) Puntos por cliente:
SELECT cliente.nombre, SUM(puntos.puntos) FROM cliente LEFT JOIN puntos ON (cliente.id=puntos.id_cliente) GROUP BY cliente.nombre;
Beneficios de esta query con la que tenías:
- También te muestra los clientes que no tienen puntos
- Si quieres puedes agregar un WHERE indicando un rango de fechas para saber qué cliente ganó puntos en un período de tiempo específico

2) Compras por cliente: (Más de los mismo)
SELECT cliente.nombre, SUM(compra.monto) FROM cliente LEFT JOIN compra ON (cliente.id=compra.id_cliente) GROUP BY cliente.nombre
Beneficios, los mismos

3) Puntos y compras por cliente:
SELECT cliente.nombre, SUM(puntos.puntos), SUM(compra.monto) FROM cliente LEFT JOIN puntos ON (cliente.id=puntos.id_cliente) LEFT JOIN compra ON (cliente.id=compra.id_cliente) GROUP BY cliente.nombre;

Y listo. Verás que mientras mejor diseñada está la estructura más fácil es obtener la información que deseas.

La verdad que no se como agradecer tu esfuerzo por ayudarme!

Te cuento que gracias a tu consejo aprendí algo sobre las estructuras de las tablas.

Perdón por tardar en contestarte, es que estuve modificando el código y esto es algo que hago solo en mis ratos libres

Gracias a tu ejemplo pude adaptar estas tablas a mi foro PHPBB3 y solucionar varios detalles que con la otra estructura no sabía como hacer. La tabla clientes es mi tabla de usuarios de PHPBB3!!

Ya tengo todo como me dijiste.
Pero sigo con el mismo problema en la consulta 3.

En vez de mostrarme:

Nombre - Total puntos - Total compras
Gustavo -------- 10 ------- 5
Carlillo --------- 5 -------- 2

Me muestra:

Nombre - Total puntos - Total compras
Gustavo -------- 10 ------- 50
Carlillo --------- 5 -------- 10



EL total de compras me lo muestra multiplicado por el total de puntos. Algo similar me pasaba con la estructura anterior. Supongo que es porque al llamar a 3 columnas de tres tablas diferentes hace el producto cartesiano entre ellas.




Buscando y buscando no encontré la solución. Puede que la solución este en hacer sub-consultas o algo así?


Saludos!!


PD: Juro que si algún día logro que mi site me deje algún billetín te voy a enviar algunos verdes

[ingcaresc]

La solucion de pragone esta muy bien, pero en la tercer consulta creo que deberian unir las dos anteriores, usar subcosultas, ya que en ellas esta el resulatado que necesitas, seria algo asi:

select a.nombre,a.Puntos,b.Monto
(SELECT cliente.nombre, SUM(puntos.puntos)as Puntos FROM cliente LEFT JOIN puntos ON (cliente.id=puntos.id_cliente) GROUP BY cliente.nombre;) A
inner join
(SELECT cliente.nombre, SUM(compra.monto)as Monto FROM cliente LEFT JOIN compra ON (cliente.id=compra.id_cliente) GROUP BY cliente.nombre) B
ON A.nombre=B.nombre

Espero que asi quede bien

[pragone]

Jajajaja.... No te preocupes Gustavo, lo hago con placer. Para mi es gratificante ver que el enlace y la poca ayuda que puedo dar son útiles

Ahora bien... tienes toda la razón. La verdad es que lo hice sin probarlo y el problema aquí son las funciones agregadoras (SUM), por lo que el tercer query que te di no sirve.

La solución de ingcaresc debería funcionar bien, aunque no la he podido probar porque no tengo instalado MySQL 5 en este ordenador sino el 4.

La única opción alternativa que se me ocurre es que si te basta con el id del cliente y no necesitas el nombre, puede hacer la query entre la tabla "puntos" y "compras" así:
SELECT puntos.id_cliente, SUM(puntos.puntos), SUM(compras.monto) FROM puntos LEFT JOIN compras ON (puntos.id_cliente=compras.id_cliente) GROUP BY puntos.id_cliente;

Problemas de esta query:
1.- No te trae el nombre del cliente
2.- Sólo te trae registros para aquellos clientes que tienen puntos registrados (se puede hacer lo mismo usando como tabla base compras si te sirve que traiga los registros de aquellos que han comprado).

Bueno... si tienes MySQL 5, la solución de ingcaresc está perfect. Si tienes MySQL 4, o usas el último query que te paso (con sus limitaciones) o (oohhh destino terrible), ejecutas dos consultas

Por otro lado, suerte con la pagina

[GsutavoDeLanus]

Ya esta!!! la consulta de ingcaresc me funciona de 10! les estoy muy agradecidos! Aprendí mucho estos últimos días y pude fusionar mis tablas con las del foro PHPBB3; ya puedo seguir con la programación de sitio. Con la estructura que me recomendó pragone pude mostrar la info que necesitaba y varias cosas mas. Me siento en deuda con ustedes y con el foro. Cuando pueda me pongo a responder por lo menos algunas dudas simple que encuentre, jej


Un abrazo!