domingo, 28 de noviembre de 2010

SQL: Filtrar por Columna Resultado de Agrupado

Nota: Para poder seguir mejor este ejemplo y poder ejecutar las consultas, te recomiendo seguir las instrucciones para configurar el ambiente de trabajo en la parte 1 del tutorial de SQL.

En este post analizaremos una pregunta que recibí en la parte 3 (Consultas con agrupados) del tutorial de SQL. La siguiente consulta obtiene lo que ha gastado en total cada cliente en la renta de films.

SELECT customer.customer_id, first_name, last_name, SUM(amount) 
FROM customer 
INNER JOIN payment ON (customer.customer_id=payment.customer_id) 
GROUP BY customer.customer_id, first_name, last_name;

Pero, ¿cómo podemos filtrar por columnas que son resultado de agrupados? Específicamente, en la consulta anterior, ¿cómo desplegamos sólo los clientes que hayan gastado 100 o más? Este dato es resultado de usar una función de agrupado; es decir, no es una columna directa de la tabla. Por ello, no lo podemos usar directamente en la cláusula where. Lo que vamos a usar son consultas anidadas. La consulta original servirá como la tabla de otra consulta. Veamos la consulta que obtiene el resultado deseado:

SELECT *
FROM
(
   SELECT customer.customer_id, first_name, last_name, 
          SUM(amount) AS gasto_total
   FROM customer
   INNER JOIN payment ON (customer.customer_id=payment.customer_id)
   GROUP BY customer.customer_id, first_name, last_name
) ClientesConPagoTotal
WHERE gasto_total >= 100;

Como puedes observar, el resultado de la consulta original se usa como la tabla fuente de la consulta externa - nota que asignamos un nombre a esta tabla interna: ClientesConPagoTotal. También nombramos la columna que contiene el resultado agregado: gasto_total. Ahora, todos las columnas de la tabla temporal ClientesConPagoTotal son visibles en la consulta externa y las podemos usar para realizar filtros, como en: WHERE gasto_total >= 100.

Saludos,
Toño.

5 comentarios:

  1. Mae, que increible tengo 15 años trabajando en Sql Server y nunca habia usado algo así, y me salvo la vida en una consulta que me estaba sacando las canas!

    gracias

    ResponderEliminar
    Respuestas
    1. Muchas gracias por tu comentario, nos da gusto que este post te haya ayudado.

      Saludos.

      Eliminar
  2. Buenas tengo una consulta en sql y ya tengo varios dias intentando eso pero nada, me da el siguiente error: Invalid use of group function... ya no se que hacer...
    Aca le dejo la sentencia, tal vez me puedas ayudar

    INSERT INTO itemreplacement

    SELECT totales.itemID,totales.longDescription, totales.stockMin,totales.stockMax,SUM(totales.itemSaleQuantity),
    totales.itemUbication,totales.itemStock,'N'

    FROM
    (SELECT i.itemID,i.longDescription, i.stockMin,i.stockMax,
    CASE (p.operationTypeID)
    WHEN '080' THEN SUM(p.saleQuantity)
    WHEN '090' THEN -SUM(p.saleQuantity)
    WHEN '100' THEN -SUM(p.saleQuantity)
    ELSE 0
    END AS itemSaleQuantity ,
    w.itemUbication,w.itemStock,p.operationTypeID

    FROM historic.posdetail p,bstore.item i LEFT JOIN bstore.warehouseitem w ON (i.itemID=w.itemID)

    WHERE p.detailInvoiceDate= CURDATE() AND p.detailTime= CURTIME() AND i.itemID=p.itemID
    GROUP BY i.itemID, p.operationTypeID) totales

    GROUP BY totales.itemID

    ON DUPLICATE KEY UPDATE itemSaleQuantity=SUM(totales.itemSaleQuantity)+itemSaleQuantity, Reemplazado='N'

    se te agradece....

    ResponderEliminar
    Respuestas
    1. Hola,

      En la consulta interna las columnas en la clausula SELECT que no son parte de GROUP BY deben ser resultado de una operacion de agrupado (por ejemplo SUM). En la consulta i.longDescription, i.stockMin, i.stockMax, w.itemUbication, w.itemStock no son parte del GROUP BY ni son resultado de una operacion de agrupado.

      Saludos.

      Eliminar