WHERE con GROUP by ROLLUP en sql server

Question

Tengo una consulta en la que uso GROUP BY ROOLUP en un campo de referencia, al filtrar los resultados con HAVING se realiza el filtro pero los totales del ROLLUP siguen siendo los totales generales y no del filtro. Su ayuda por favor.

     SELECT cnt.cntReferencia,sum(cnt.cntDebito) AS 'Ingresos', sum(cnt.cntCredito) AS 'Egresos', sum(cnt.cntDebito)-sum(cnt.cntCredito) as 'Saldo'  
            FROM     dbo.cntMaestro AS cnt  
            WHERE    cntCuenta = @cuenta and cntFecha <= @dateFin   
			GROUP by ROLLUP (cntReferencia)   
			HAVING   sum(cntCredito)-sum(cntDebito) <> 0  
			ORDER by cntReferencia  

Resultados con HAVING:

Answer 1

Hola,

Este foro es para discusiones en inglés. Si no puede debatir en inglés, intente encontrar el foro relativo en español en MSDN donde se encuentran los foros que no están en inglés.

Good day,

This forum is for discussions in English. If you cannot discuss in English then please try to find the Spanish relative forum at MSDN where the non-English forums are.

Using Google translator this is what I understand your question is:

I have a query in which I use GROUP BY ROOLUP in a reference field, when filtering the results with HAVING the filter is performed but the totals of the ROLLUP are still the general totals and not of the filter. Your help please.

It seems in first glance that you are using the wrong filter. Here is the short explanation of the difference quote from Microsoft Learn:

A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows.

If you can provide queries to create the table and insert some sample rows + the expected result set according to the sample rows, then we will be able to check exactly what you need and provide a working solution

Answer 2

This happens because GROUP BY is logically performed before HAVING. I think this query should give you the result you want:

WITH CTE AS (
   SELECT cnt.cntReferencia,sum(cnt.cntDebito) AS 'Ingresos', sum(cnt.cntCredito) AS 'Egresos', sum(cnt.cntDebito)-sum(cnt.cntCredito) as 'Saldo'
   FROM   dbo.cntMaestro AS cnt
   WHERE  cntCuenta = @cuenta and cntFecha <= @dateFin 
   GROUP  BY cntReferencia
) 
SELECT cntReferencia, SUM(Ingresso), SUM(Egressos), SUM(Saldo)
FROM   CTE
WHERE  Saldo <> 0
GROUP  BY ROLLUP (cntReferencia)
ORDER BY cntReferencia

Answer 3

Hi，@Jaime Mora

Welcome to Microsoft T-SQL Q&A Forum!

As you wrote, group by is performed before the where filter, the GROUP BY ROLLUP grouping type allows you to group by subtotals and totals, when you use that aggregate grouping type, it is effectively equivalent to creating the grouping for your cntReferencia Level.
if I guessed correctly, cntReferencia should be grouped and there will be null, because this is the operation of summarizing it. After you group, having will continue to filter the records of groupby, so the last filtered out by having is actually you. The first step is to group the columns that contain the summary column, so the totals will not be as expected and you should choose the appropriate filter.

Para que sea más fácil de leer, usé google para traducirlo a su idioma.
Como escribiste, agrupar por se realiza antes del filtro where, el tipo de agrupación GROUP BY ROLLUP te permite agrupar por subtotales y totales, cuando usas ese tipo de agrupación agregada, es efectivamente equivalente a crear la agrupación para tu cntReferencia Level, si Supuse correctamente, cntReferencia debe agruparse y habrá nulo, porque esta es la operación de resumirlo. Después de agrupar, el tener continuará filtrando los registros de groupby, por lo que el último filtrado por tener es en realidad usted. El primer paso es agrupar las columnas que contienen la columna de resumen, por lo que los totales no serán los esperados y deberá elegir el filtro adecuado.
Por favor refiérase a este enlace.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.