MySql - SUM COM ROLLUP - Calcular o subtotal / total

Neste artigo, vamos mostrar 3 exemplos de como utilizar a função "SUM" acompanhada do comando "WITH ROLLUP" para calcular o subtotal / total em uma consulta.
Caso ainda não conheça a função "SUM", veja o artigo "Mysql SUM - Soma "

Caso tenha interesse, faça o download ou veja o script deste artigo no github.
O script também está disponível para execução no Sql Fiddle e foi testado no Mysql 5.1.61.

1º  Exemplo

Vamos calcular as despesas com fornecedor para cada segmento de uma empresa.
Iremos exibir o subtotal por segmento: "Papelaria e informática", "Marcenaria", "Serralheria" e "Limpeza e higiêne".
Também vamos exibir no final da consulta o total geral para todos os segmentos.
Neste exemplo, utilizaremos a tabela "tb_fornecedor". Veja a imagem da tabela a seguir:

Solução

Vamos utilizar a função "SUM" acompanhada de "GROUP BY" para fazer a quebra por segmento. Além disso vamos utilizar o "WITH ROLLUP" para exibir o subtotal embaixo de cada quebra e o total geral no último registro.

Veja a sentença abaixo:

SELECT
    segmento,
    produto,
    SUM(valor)
FROM tb_fornecedor
GROUP BY  segmento, produto WITH ROLLUP;

Após a execução da sentença teremos o resultado exibido a seguir:

2º  Exemplo

Para facilitarmos a interpretação do resultado da consulta vamos alterar o conteúdo da primeira coluna:

• Nos registros que exibirem o subtotal, a primeira coluna será acompanhada da palavra "Subtotal" e do nome do segmento;
• O ultimo registro da primeira coluna se chamará "Total Geral";
• Nos outros registros o nome do segmento será mantido;

Solução

Veja a sentença abaixo: 

SELECT
    IF
    (
      segmento IS NULL,
      'Total Geral',
      IF (produto IS NULL, CONCAT('Subtotal - ', segmento), segmento)
    )
    AS segmento,
    produto,
    SUM(valor) as valor
FROM tb_fornecedor
GROUP BY  segmento, produto WITH ROLLUP;

Após a execução da sentença teremos o resultado exibido a seguir:

• Utilizamos a condição "IF" para verificar onde o segmento era nulo e substituímos por "Total Geral";
• Para segmento não nulo fizemos uma outra verificação. Foi encadeado um outro "IF" para verificar onde o produto era nulo e substituímos por "Subtotal" acompanhado do  nome do segmento, caso o produto não fosse nulo mantivemos o nome do segmento;
• Observação: utilizamos a função "CONCAT" para unir a palavra "Subtotal" ao conteúdo da coluna segmento;

3º  Exemplo

Não é possível utilizar "WITH ROLLUP" com "ORDER BY" na mesma consulta. Neste exemplo, vamos mostrar como ordenar colunas utilizando uma subquery (subconsulta). Vamos ordenar as colunas "segmento" e "produto" em ordem decrescente.

Solução

Veja a sentença abaixo: 

SELECT
COALESCE(segmento, 'TOTAL GERAL')  AS segmento,
CASE
 WHEN ((segmento IS NOT NULL) AND (produto IS NULL))
  THEN CONCAT('Subtotal - ', segmento)
  WHEN (segmento IS NOT NULL AND produto IS NOT NULL)
  THEN produto
END AS produto,
valor FROM
(
 SELECT
 segmento, 
 produto, 
 SUM(valor) AS valor
 FROM tb_fornecedor
 GROUP BY segmento, produto WITH ROLLUP
) TOTAL
ORDER BY
CASE
 WHEN(segmento IS NOT NULL) THEN 0
 ELSE 1
END,
segmento DESC,
CASE
 WHEN(produto IS NOT NULL) THEN 0
 ELSE 1
END,
produto DESC;

A subquery que criamos está destacada na cor azul e posicionada entre parenteses, esta subquery recebeu o alias (apelido) de TOTAL.

Observação: perceba que no "ORDER BY" utilizamos duas vezes o "CASE WHEN":

• o primeiro para que o "TOTAL GERAL" fosse exibido no último registo da coluna "segmento" independente da ordem alfabética;
• o segundo para que o "Subtotal" fosse exibido no final de cada segmento da coluna "produto" independente da ordem alfabética;

Após a execução da sentença, teremos o resultado exibido a seguir:

Deixe o seu comentário ou sugestão.
Gostou?  Siga no Google +  ou Facebook.

Mysql - função count

O artigo apresenta 8 exemplos de como utilizar a função de agrupamento "count", das seguintes formas:

• COUNT(*);
• COUNT(nome_da_coluna);
• COUNT COM GROUP BY;
• COUNT(DISTINCT(nome_da_coluna));

Caso tenha interesse faça o download dos scripts ou veja no GitHub. 

Nestes exemplos, vamos utilizar a tabela "tb_ordem_servico" que armazena informações sobre ordens de serviço (solicitações de serviços). Exibimos a seguir a imagem da tabela e a descrição das colunas:

• cod_ordem: código da ordem de serviço;
• cod_servico: código do serviço que será prestado;
• data_inicial: data da solicitação da ordem de serviço;
• data_final: data de encerramento da ordem de serviço;

FUNÇÃO COUNT(*)

A função COUNT(*) retorna o número de registros (linhas) de uma tabela.

SINTAXE

SELECT COUNT(*) FROM nome_da_tabela;

1º Exemplo

Exibir quantidade de registros da tabela "tb_ordem_servico".

Solução

Para exibir a quantidade de registros, vamos executar a sentença abaixo:

SELECT COUNT(*) FROM tb_ordem_servico;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

O nome da coluna recebeu o nome da função "count". Caso seja preciso alterar o nome da coluna, por exemplo, para "quantidade", crie uma alias "apelido". Para isso coloque um AS após a função COUNT(*).

SELECT COUNT(*) AS quatidade FROM tb_ordem_servico;

Após a execução do comando, o nome da coluna será exibido como "quantidade", veja a imagem abaixo:

FUNÇÃO COUNT(nome_da_coluna)

A função COUNT(nome_da_coluna) retorna o número valores de uma coluna. Valores nulos não entram na contagem.

SINTAXE

SELECT COUNT(nome_da_coluna) FROM nome_da_tabela;

2º Exemplo
Exibir a quantidade de registros onde o código do serviço seja do tipo "A".

Solução
Para fazer a contagem, executamos a sentença abaixo:

SELECT 
COUNT(cod_servico) AS quantidade
FROM tb_ordem_servico
WHERE cod_servico = 'A';

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

3º Exemplo

Exibir a quantidade de ordens de serviços que já foram finalizadas. 

Solução
As ordens de serviços finalizadas são aquelas que possuem uma data de encerramento. Logo vamos utilizar a função COUNT na coluna data_final
Para fazer a contagem, executamos a sentença abaixo: 

SELECT 
COUNT(data_final) AS quantidade
FROM tb_ordem_servico;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Observação: perceba que os valores nulos não são incluídos na contagem

4º Exemplo

Exibir a quantidade de ordens de serviços que não foram finalizadas.

Solução
As ordens de serviços não finalizadas são aquelas que ainda não possuem data de encerramento,  logo vamos fazer a contagem dos registros onde o valor a coluna "data_final" seja nulo. 

Como precisamos contar valores nulos, vamos utilizar "COUNT(*)", pois COUNT(nome_da_coluna) ignora valores nulos.


SELECT 
COUNT(*) AS quantidade
FROM tb_ordem_servico WHERE data_final IS NULL;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

5º Exemplo

Distribuição por tipo de serviço:

• Quantidade de solicitações para o serviço "A";
• Quantidade de solicitações para o serviço "B";
• Quantidade de solicitações para o serviço "C";

Solução

Para exibir a distribuição por tipo de serviço vamos agrupar os dados da coluna "cod_servico" através do comando "group by".

SELECT
cod_servico,
COUNT(cod_servico) AS quantidade
FROM tb_ordem_servico 
GROUP BY cod_servico;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

6º Exemplo

Quantidade de ordens finalizadas por mês e ano (mm/aaaa):

Solução

Para exibir a distribuição por mês e ano vamos utilizar a funcão "COUNT(nome_da_coluna)" e agrupar os dados da coluna através do comando "group by". 
A coluna utilizada será a "data_final", pois indica a data de encerramento da ordem de serviço. 
A função "date_format" é utilizada para exibir a data no formato (mm/aaaa).

SELECT
CASE WHEN data_final IS NOT NULL
    THEN  date_format(data_final, '%m/%Y')
END mes_ano,
COUNT(data_final) AS qtd_os
FROM tb_ordem_servico
GROUP BY mes_ano;

Após a execução da sentença obtemos o seguite resultado.

Observação: repare que os valores nulos não entraram na contagem, pois utilizamos a função COUNT(nome_da_coluna).

Para que os valores nulos entrem na contagem vamos utilizar a função COUNT(*)

SELECT

date_format(data_final, '%m/%Y') AS mes_ano,
COUNT(*) AS qtd_os
FROM tb_ordem_servico
GROUP BY date_format(data_final, '%m/%Y');

Após a execução do comando, os valores nulos entram na contagem.

Podemos colocar a mensagem "Pendente" para indicar a quantidade de ordens de serviço que não possuem data de encerramento.

SELECT
CASE WHEN data_final IS NOT NULL
    THEN  date_format(data_final, '%m/%Y')
    ELSE 'Pendente'
END mes_ano,
COUNT(*) AS qtd_os
FROM tb_ordem_servico
GROUP BY mes_ano;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Se não quisermos exibir a contagem de valores nulos, utilizamos um filtro no "WHERE":

SELECT
date_format(data_final, '%m/%Y') AS mes_ano,
COUNT(data_final) AS qtd_os
FROM tb_ordem_servico
WHERE data_final IS NOT NULL
GROUP BY mes_ano;

Após a execução do comando, teremos o resultado, conforme exibido na imagem a seguir:

Caso você queira ordenar por ano e mês em ordem crescente, utilize o comando "ORDER BY". 
Repare que no "ORDER BY" foi invertidos os parâmetros do date_format (destacado em azul): para ano e depois mês. Para que seja exibido primeiro os meses de 2013 e depois o meses de 2014.

SELECT
date_format(data_final, '%m/%Y') AS mes_ano,
COUNT(*) AS qtd_os_finalizadas
FROM tb_ordem_servico
WHERE data_final IS NOT NULL
GROUP BY date_format(data_final, '%m/%Y')
ORDER BY date_format(data_final, '%Y/%m');

Após executar a sentença, teremos o resultado, conforme exibido na imagem a seguir:

7º Exemplo

Quantidade de ordens finalizadas por mês e ano (mm/aaaa) e por tipo de serviço.

Solução:

Perceba que neste exemplo, estamos fazendo o agrupamento de 2 colunas. Vamos utilizar o COUNT(*) para a contagem, e o "group by" para o agrupamento.

SELECT
date_format(data_final, '%m/%Y') AS mes_ano, 
cod_servico,
COUNT(*) AS qtd_os
FROM tb_ordem_servico
WHERE data_final IS NOT NULL
GROUP BY mes_ano, cod_servico;

Após executar a sentença, teremos o resultado, conforme exibido na imagem a seguir:

Caso você queira ordenar em crescente o ano/mês e o serviço, utilize o comando "ORDER BY".

Após executar a sentença, teremos o resultado, conforme exibido na imagem a seguir:

SELECT
date_format(data_final, '%m/%Y') AS mes_ano,
cod_servico,
COUNT(*) AS qtd_os_finalizadas
FROM tb_ordem_servico
WHERE data_final IS NOT NULL
GROUP BY date_format(data_final, '%m/%Y'), cod_servico
ORDER BY date_format(data_final, '%Y/%m'), cod_servico;

Após a execução da consulta, será exibido o resultado a seguir:

FUNÇÃO COUNT(DISTINCT(nome_da_coluna))

SINTAXE

SELECT COUNT(DISTINCT(nome_da_coluna)) FROM nome_da_tabela;

Neste exemplo, vamos explicar como utilizar a função "distinct" dentro da função "count".

Utilizando a função "count" desta forma, os valores iguais só são contados uma vez.

Pareceu complicado? Veja o exemplo.

8º Exemplo

Observação: Incluímos a coluna cliente na tabela "tb_ordem_servico".

Exibir quantas empresas fizeram solicitação de ordem de serviço.

Solução:

Se observarmos a coluna "cliente" veremos que 3 empresas fizeram solicitação a "Padaria P", a  "Marcenaria M" e o "HOSPITAL H".

Para retornar o resultado através de comando SQL executamos a sentença abaixo:

SELECT COUNT(DISTINCT(cliente))  FROM tb_ordem_servico;

Após executar a sentença, teremos o resultado, conforme exibido na imagem a seguir:

Faça um comentário no final do artigo, suas sugestões ou críticas serão bem-vindas.

MySql SUM - Soma

A função SUM retorna a soma de valores de uma coluna.

SINTAXE

SELECT SUM(nome_da_coluna) FROM nome_da_tabela;

Para os 6 exemplos abaixo, utilizaremos a tabela "tb_fornecedor". Veja a imagem abaixo:

Caso tenha interesse faça o download dos exemplos ou veja os scripts no GitHub.

1º Exemplo

Cenário: um funcionário quer calcular o valor total das despesas de uma empresa.

Solução: o valor das despesas estão armazenados na coluna valor, logo devemos somar os valores desta coluna.

Para calcularmos a soma, executamos a sentença abaixo:

SELECT SUM(valor) FROM tb_fornecedor;

Após a execução, teremos o gasto total com as despesas. Conforme podemos visualizar na imagem abaixo:

O nome da coluna aparece como "SUM(valor)", mas vamos supor que precisamos que seja exibido "total".

Podemos modificar o nome desta coluna, ou seja, criar um alias (apelido). Colocamos o alias depois do "AS" 

SELECT SUM(valor) AS total FROM tb_fornecedor;

Após a execução, o nome da coluna será exibido como "total".

2º Exemplo

Cenário: O funcionário de uma empresa quer calcular o valor total das despesas por segmento, ou seja:

• Total de gastos com o segmento Papelaria e informática
• Total de gastos com o segmento Marcenaria
• Total de gastos com o segmento Serralheria
• Total de gastos com o segmento Limpeza e higiene

Solução: antes de efetuarmos a soma, devemos agrupar os segmentos que estão armazenados na coluna "segmento". Para agruparmos utilizaremos o " GROUP BY".

Para calcularmos a soma por segmento, executamos a sentença abaixo:

SELECT segmento, SUM(valor) AS total 
FROM tb_fornecedor 
GROUP BY segmento;

Após a execução, teremos o gasto total com as despesas agrupado por segmento. Conforme podemos visualizar na imagem abaixo:

Para ordenar o total em ordem crescente, ou seja, do valor menor para o maior, deve-se usar "ORDER BY" seguido do nome da coluna, neste caso a coluna ordenada será 'total'.

Para ordenar os valores, utilizamos a sentença abaixo:

SELECT 
segmento, 
SUM(valor) AS total 
FROM tb_fornecedor 
GROUP BY segmento
ORDER BY total;

Após a execução, teremos os valores em ordem crescente. Conforme podemos visualizar na imagem abaixo:

Para ordenar o total em ordem decrescente, ou seja, do maior valor para o menor devemos acrescentar o "DESC" depois do "ORDER BY".

Para ordenar os totais em ordem decrescente, utilizamos a sentença abaixo:

SELECT 
segmento, 
SUM(valor) AS total 
FROM tb_fornecedor 
GROUP BY segmento
ORDER BY total DESC;

Após a execução, teremos os valores totais em ordem decrescente.  Conforme podemos visualizar na imagem abaixo:

3º Exemplo

Cenário: Este cenário é um pouco parecido com o anterior,  vamos calcular as despesas por segmento, mas agora considerando a quantidade de produtos.
Observação: perceba que foi incluída a coluna quantidade na tabela "tb_fornecedor".

Solução: dentro da função "SUM" devemos fazer a multiplicação da quantidade pelo valor do produto. Exemplo: gastamos R$ 335,00 com o segmento "Limpeza e higiene", pois compramos

• R$ 15,00 de detergente     (3 *   5,00);
• R$ 200,00 de desinfetante (5 * 40,00);
• R$ 120,00 de papel toalha (2 * 60,00);

Total para o segmento "Limpeza e higiene": 15,00 + 200,00 + 120,00 = 335,00

Para calcularmos a soma por segmento, executamos a sentença abaixo:

SELECT 
segmento, 
SUM(quantidade * valor) AS total 
FROM tb_fornecedor 
GROUP BY segmento
ORDER BY total;

Após a execução, teremos os totais por segmento. Conforme podemos visualizar na imagem abaixo:

4º Exemplo

Cenário: Vamos calcular o total das despesas por mês. Neste caso, para "setembro / 2013", "outubro / 2013" e "novembro / 2013".

O primeiro passo é saber como extrair o mês e o ano de uma data. 

Para extrair o mês e o ano de uma data utilizaremos a função "extract".

Exemplo da utilização de extract:

SELECT

data, 

extract(month from data) AS mes,

extract(year from data) AS ano

FROM tb_fornecedor;

Significado:

extract(parte_da_data from nome_da_coluna)

• parte_da_data: podemos passar qual a parte da data que queremos, por exemplo,  day para o dia ou year para o ano, entre outros.
• nome_da_coluna: nome da coluna de uma tabela. Neste caso vamos escolher a coluna "data" da tabela "tb_fornecedor".

Após a execução, teremos uma coluna com o ano, e uma coluna com o mês. Conforme podemos visualizar na imagem abaixo:

Agora que já sabemos utilizar as função "extract", vamos utilizá-la para fazer o calculo das despesas para os meses de setembro, outubro e novembro de 2013.

SELECT 

extract(month from data) AS mes,
SUM(quantidade * valor) AS total 
FROM tb_fornecedor
WHERE extract(year from data) = 2013 
GROUP BY mes
ORDER BY mes;

Após a execução, teremos o total para cada mês.

Caso queiramos exibir os meses por extenso e na língua portuguesa, ou seja, setembro, outubro e novembro devemos configurar a variável de sistema do mysql "lc_time_name". Em caso de dúvida veja o artigo "Mysql - formatar data e hora":

SELECT

/*Formata a data em português devido a configuração da variável de sistema do mysql chamada "lc_time_names". Em caso de dúvida veja o artigo de formatação de data*/ 

date_format(data, '%M/%Y') AS mes, 

SUM(quantidade * valor) AS total 

FROM tb_fornecedor

WHERE extract(year FROM data) = 2013 

GROUP BY  mes

/*Orderna pela ordem numérica do mês (...9,10, 11)* observe o "m" do date_format em minúsculo*/ 

ORDER BY date_format(data, '%m/%Y') ;

Após a execução, o mês será exibido por extenso. Conforme podemos visualizar na imagem abaixo:

5º Exemplo

Cenário: Vamos calcular o total das despesas por mês e por segmento. Para isso vamos agrupar por mês e segmento, ou seja o "GROUP BY" será acompanhado pelo mês e pelo segmento.

SELECT

extract(month FROM data)  AS mes,

segmento, 

SUM(quantidade * valor) AS total 

FROM tb_fornecedor

WHERE extract(year FROM data) = 2013 

GROUP BY mes, segmento

ORDER BY mes, segmento;

Após a execução, as despesas serão agrupadas por mês e segmento. Conforme podemos visualizar na imagem abaixo:

Caso queiramos exibir os meses por extenso, ou seja, setembro, outubro e novembro podemos utilizar a função "date_format" na sentença:

SELECT

/*Formata a data em português devido a configuração da variável de sistema do mysql chamada "lc_time_names". Em caso de dúvida veja o artigo de formatação de data*/ 

date_format(data, '%M/%Y') AS mes, 

segmento, 

SUM(quantidade * valor) AS total 

FROM tb_fornecedor

WHERE extract(year FROM data) = 2013 

GROUP BY mes,  segmento

/*Orderna pela ordem numérica do mês (...9,10, 11)* observe o "m" do date_format em minúsculo*/ 

ORDER BY date_format(data, '%m/%Y'), segmento;

Após a execução, o meses serão exibidos por extenso. Conforme podemos visualizar na imagem abaixo:

6º Exemplo

Cenário: queremos saber quais os meses em que as despesas ultrapassaram R$2000, ou seja foram maior que R$2000?

Solução: para sabermos quais os meses em que as despesas ultrapassaram R$2000 vamos adicionar a clausula "HAVING" a sentença.

SELECT

/*Formata a data em português devido a configuração da variável de sistema do mysql chamada "lc_time_names". Em caso de dúvida veja o artigo de formatação de data*/ 

date_format(data, '%M/%Y') AS mes,

SUM(quantidade * valor) AS total 

FROM tb_fornecedor

WHERE extract(year FROM data) = 2013 

GROUP BY mes  

HAVING SUM(quantidade * valor) > 2000

/*Orderna pela ordem numérica do mês (...9,10, 11)* observe o "m" do date_format em minúsculo*/ 

ORDER BY date_format(data, '%m/%Y');

Após a execução, só serão exibidos os meses nos quais as despesas ultrapassaram R$ 2000,00. Conforme podemos visualizar na imagem abaixo:

Em breve, postarei mais exemplos da função sum.