Início / dba / Perguntas / 198055
Accepted
user45867
Asked: 2018-02-16 12:39:13 +0800 CST

Consulta SQL - Como selecionar para exibir grupo, total de grupo, total geral?

  • 772

Eu vi alguns conselhos, mas quero saber a melhor maneira de selecionar um grupo, total de grupo, total geral de uma tabela de dados SEM usar uma subconsulta ou junção desnecessária.

Meu pensamento inicial foi algo assim:

select   product_family, 
         sum(widgets), 
         sum(widgets) over ()
from     table.widget
group by product_family

ou o seguinte:

select   product_family, 
         sum(widgets), 
         sum(widgets) over (partition by all_field)
from     table.widget
group by product_family

Obviamente nenhum desses trabalhos. Eu sei que uma partição pode ser de uma ordem mais alta do que as linhas/grupos reais, mas não como particionar por "todos" além de deixá-lo em branco como o primeiro exemplo. No entanto, rompe com o grupo por afirmação.

O melhor que consigo encontrar é algo assim:

select product_family, 
       family_sum, 
       sum(family_sum) over () as grand_sum 
from (
      select   product_family, sum(widgets)
      from     table.widget
      group by product_family
     ) as A

Isso ainda envolve uma subconsulta, o que é bom. Eu apenas sinto que estou perdendo uma função fácil aqui.

sql-server group-by

2 respostas

  1. GROUP BY ROLLUPfaz o que você quer.

    Como um exemplo:

    SELECT o.schema_id
    , type_desc
    , [Count Of Objects] = COUNT(o.object_id)
FROM sys.objects o
GROUP BY ROLLUP (o.schema_id, o.type_desc)

    Isso produz a seguinte saída:

    ╔═══════════╦════════════════════════╦════════════ ══════╗
║ schema_id ║ type_desc ║ Contagem de objetos ║
╠═══════════╬════════════════════════╬════════════ ══════╣
║ 1 ║ PRIMARY_KEY_CONSTRAINT ║ 7 ║
║ 1 ║ SERVICE_QUEUE ║ 3 ║
║ 1 ║ USER_TABLE ║ 8 ║
║ 1 ║ NULO ║ 18 ║
║ 4 ║ TABELA_INTERNA ║ 16 ║
║ 4 ║ SYSTEM_TABLE ║ 72 ║
║ 4 ║ NULO ║ 88 ║
║ NULO ║ NULO ║ 106 ║
╚═══════════╩════════════════════════╩════════════ ══════╝

    O NULLmostrado nas duas primeiras colunas representa os valores de roll-up.

    Para torná-lo "bonito", você pode usar algumas substituições para os NULLvalores acumulados:

    SELECT [Schema Name] = CASE 
        WHEN s.name IS NULL THEN 
            '[Grand Total]' 
        ELSE s.name 
        END
    , [Object Type] = CASE 
        WHEN o.type_desc IS NULL THEN 
            '[Total - ' + COALESCE(s.name, 'Overall') + ']' COLLATE SQL_Latin1_General_CP1_CI_AS 
        ELSE o.type_desc 
        END
    , [Count of Objects] = COUNT(o.object_id)
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
GROUP BY ROLLUP (s.name, o.type_desc);

    ╔═══════════════╦════════════════════════╦════════ ══════════╗
║ Nome do esquema ║ Tipo de objeto ║ Contagem de objetos ║
╠═══════════════╬════════════════════════╬════════ ══════════╣
║ dbo ║ PRIMARY_KEY_CONSTRAINT ║ 7 ║
║ dbo ║ SERVICE_QUEUE ║ 3 ║
║ dbo ║ USER_TABLE ║ 8 ║
║ dbo ║ [Total - dbo] ║ 18 ║
║ sys ║ INTERNAL_TABLE ║ 16 ║
║ sys ║ SYSTEM_TABLE ║ 72 ║
║ sys ║ [Total - sys] ║ 88 ║
║ [Total Geral] ║ [Total - Geral] ║ 106 ║
╚═══════════════╩════════════════════════╩════════ ══════════╝
    • 5
  2. Best Answer

    GROUP BY ROLLUPou - ainda melhor - GROUP BY GROUPING SETSé melhor se você quiser uma linha adicional com o total geral.

    Se você deseja o total geral em cada linha - em outra coluna - sua consulta precisa de um pequeno ajuste. Você pode usar o agregado SUM(widgets)em uma função de janela:

    select   product_family, 
         sum(widgets)              as total, 
         sum(sum(widgets)) over () as grand_total
from     table.widget
group by product_family ;
    • 4

relate perguntas