Agregação: Grouping with cube
Exemplo de utilização de agregação GROUP BY CUBE em T-SQL
drop table #AggregationTable
SELECT [IntegradoCRM], [AgenteAlteracao], [NomeEntidade], [Operacao], COUNT(*) AS NumeroRegistos
, GROUPING([IntegradoCRM]) 'isIntegradoCRMGroup'
, GROUPING([AgenteAlteracao]) 'isAgenteAlteracaoGroup'
, GROUPING([NomeEntidade]) 'isNomeEntidadeGroup'
, GROUPING([Operacao]) 'isOperacaoGroup'
, Grouping_ID ([IntegradoCRM], [AgenteAlteracao], [NomeEntidade], [Operacao]) 'isGroupingRow'
INTO #AggregationTable
FROM [Pec_Stagingarea_to_CRM_Online].[pec].[Integracao]
WHERE IntegradoCRM = 0
GROUP BY [IntegradoCRM], [AgenteAlteracao], [Operacao], [NomeEntidade]
WITH CUBE;
select ISNULL([NomeEntidade], 'Total registos por processar'), SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isIntegradoCRMGroup = 1 AND isOperacaoGroup = 1 AND isAgenteAlteracaoGroup = 1
GROUP BY [NomeEntidade]
order by [NomeEntidade]
select ISNULL([NomeEntidade], 'Total'), AgenteAlteracao, SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isIntegradoCRMGroup = 1 AND isOperacaoGroup = 1 AND isAgenteAlteracaoGroup = 0
GROUP BY AgenteAlteracao, [NomeEntidade]
order by AgenteAlteracao, [NomeEntidade]
select ISNULL([NomeEntidade], 'Total'), AgenteAlteracao, [Operacao], SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isOperacaoGroup = 0 AND isAgenteAlteracaoGroup = 0 AND isIntegradoCRMGroup = 0
GROUP BY AgenteAlteracao, [NomeEntidade], [Operacao]
order by AgenteAlteracao, [NomeEntidade], [Operacao]
select [Operacao], SUM(NumeroRegistos) AS Numeroregistos
FROM #AggregationTable
WHERE isOperacaoGroup = 0 AND isAgenteAlteracaoGroup = 1 AND isNomeEntidadeGroup = 0 AND isIntegradoCRMGroup = 0
GROUP BY [Operacao]
GOReferência: SELECT - GROUP BY- Transact-SQL
Agregação: Grouping with cube
![Agregação: Grouping with cube]()
Reviewed by Silvia Pinhão Lopes
on
26.6.18
Rating: 5
