TÓPICO 5 – FUNÇÕES AGREGADAS

  • Subtópico 5.1 – Identificar as funções de agrupamento
  • Subtópico 5.2 – Descrever o uso das funções de agrupamento
  • Subtópico 5.3 – Agrupar dados usando a cláusula GROUP BY
  • Subtópico 5.4 – Incluir/excluir linhas agrupadas usando a cláusula HAVING

Neste quinto tópico referente a certificação de SQL Básico, falarei sobre as funções agregadas, como, quando e onde usa-las.

Antes de entrar de fato no assunto “Funções agregadas” vou mencionar algumas dicas que podem ser úteis no exame:

DICA.01 – É possível usar funções escalares dentro de funções agregadas. Por funções escalares entende-se que são funções que retornam apenas um valor, como TO_NUMBER e TO_CHAR. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIGZ1bsOnw6NvIGVzY2FsYXIgZGVudHJvIGRlIHVtYSBmdW7Dp8OjbyBhZ3JlZ2FkYQogU0VMRUNUIENPVU5UKFRPX05VTUJFUihJREFERSkpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tIGZ1bsOnw6NvIGFncmVnYWRhIGRlbnRybyBkZSB1bWEgZnVuw6fDo28gZXNjYWxhcgogU0VMRUNUIFRPX05VTUJFUihDT1VOVChJREFERSkpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwpESUNBLjAyIOKAkyBRdWFuZG8gc2UgdXNhIDIgZnVuw6fDtWVzIGFncmVnYWRhcyB1bWEgZGVudHJvIGRhIG91dHJhLCBvIG9wZXJhZG9yIEdST1VQIEJZIHNlIGZheiBuZWNlc3PDoXJpby4gRXg6CgogLS0gZ2VyYSB1bSBlcnJvCiBTRUxFQ1QgTUFYKEFWRyhTQUxBUklPKSkKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gbsOjbyBnZXJhIGVycm8gCiBTRUxFQ1QgTUFYKEFWRyhTQUxBUklPKSkKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBTQUxBUklP” hover_enabled=”0″]IC0tIGZ1bsOnw6NvIGVzY2FsYXIgZGVudHJvIGRlIHVtYSBmdW7Dp8OjbyBhZ3JlZ2FkYQogU0VMRUNUIENPVU5UKFRPX05VTUJFUihJREFERSkpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tIGZ1bsOnw6NvIGFncmVnYWRhIGRlbnRybyBkZSB1bWEgZnVuw6fDo28gZXNjYWxhcgogU0VMRUNUIFRPX05VTUJFUihDT1VOVChJREFERSkpCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwpESUNBLjAyIOKAkyBRdWFuZG8gc2UgdXNhIDIgZnVuw6fDtWVzIGFncmVnYWRhcyB1bWEgZGVudHJvIGRhIG91dHJhLCBvIG9wZXJhZG9yIEdST1VQIEJZIHNlIGZheiBuZWNlc3PDoXJpby4gRXg6CgogLS0gZ2VyYSB1bSBlcnJvCiBTRUxFQ1QgTUFYKEFWRyhTQUxBUklPKSkKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gbsOjbyBnZXJhIGVycm8gCiBTRUxFQ1QgTUFYKEFWRyhTQUxBUklPKSkKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBTQUxBUklP[/et_pb_dmb_code_snippet]

DICA.03 – A clausula WHERE não pode ter funções agregadas. Mas pode ter funções escalares. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIGdlcmEgdW0gZXJybwogU0VMRUNUICogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIFdIRVJFIEFWRyhTQUxBUklPKSA+IDEwMDAgCgogLS0gbsOjbyBnZXJhIGVycm8KIFNFTEVDVCAqIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBXSEVSRSBUT19OVU1CRVIoU0FMQVJJTykgPiA1MDAw” hover_enabled=”0″]IC0tIGdlcmEgdW0gZXJybwogU0VMRUNUICogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIFdIRVJFIEFWRyhTQUxBUklPKSA+IDEwMDAgCgogLS0gbsOjbyBnZXJhIGVycm8KIFNFTEVDVCAqIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBXSEVSRSBUT19OVU1CRVIoU0FMQVJJTykgPiA1MDAw[/et_pb_dmb_code_snippet]

DICA.04 – O HAVING permite o uso de subqueries. Mais adiante falarei sobre o HAVING

DICA.05 – Com exceção do COUNT(*), Funções agregadas ignoram valores NULL.

1) FUNÇÕES AGREGADAS MAIS COMUNS

1.1) COUNT(E1)

Determina o número de ocorrências de valores não-nulos. Caso o valor da expressão E1 seja NULL, este não será contabilizado pelo COUNT. Quando se usa COUNT(*), é retornado o número de linhas da tabela (Caso uma tabela possua 5 linhas, mas todas com valores NULL, o COUNT(*) retornará 5). Quando se usa COUNT(NOME_COLUNA), é retornado o número de valores não-nulos dessa coluna.

OBS: Pode-se usar as palavras DISTINCT e ALL com o COUNT. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIGEgdGFiZWxhIHBvc3N1aSAyMCBsaW5oYXMsIG1hcyBhIGNvbHVuYSAiaWRhZGUiIHBvc3N1aSAKIC0tIHVtIHZhbG9yIE5VTEwuIGEgcXVlcnkgYWJhaXhvIHJldG9ybmEgMjAKIFNFTEVDVCBDT1VOVCgqKSBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tIGNvbW8gYSBjb2x1bmEgImlkYWRlIiBwb3NzdWkgdW0gdmFsb3IgTlVMTCwgYSBxdWVyeSBhYmFpeG8KIC0tIHJldG9ybmEgMTkgCiBTRUxFQ1QgQ09VTlQoSURBREUpIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gRElTVElOQ1QgY29udGEgb3MgdmFsb3JlcyBkaXN0aW50b3MgYXBlbmFzCiAtLSBhIHRhYmVsYSBwb3NzdWkgNTAwIHJlZ2lzdHJvcywgbWFzIGFwZW5hcyA2IHZhbG9yZXMKIC0tIGRpc3RpbnRvcyBubyBjYW1wbyBMT0dJTixsb2dvIG8gc2VsZWN0IGFiYWl4byByZXRvcm5hcsOhIDYKIFNFTEVDVCBDT1VOVChESVNUSU5DVCBMT0dJTikgRlJPTSBUQUJfQVVESVRPUklBX0xPR0lOCgogLS0gQUxMIGNvbnRhIHRvZG9zIG9zIHZhbG9yZXMgbsOjby1udWxvcyByZXBldGlkb3MKIFNFTEVDVCBDT1VOVChBTEwgTE9HSU4pIEZST00gVEFCX0FVRElUT1JJQV9MT0dJTg==” hover_enabled=”0″]IC0tIGEgdGFiZWxhIHBvc3N1aSAyMCBsaW5oYXMsIG1hcyBhIGNvbHVuYSAiaWRhZGUiIHBvc3N1aSAKIC0tIHVtIHZhbG9yIE5VTEwuIGEgcXVlcnkgYWJhaXhvIHJldG9ybmEgMjAKIFNFTEVDVCBDT1VOVCgqKSBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tIGNvbW8gYSBjb2x1bmEgImlkYWRlIiBwb3NzdWkgdW0gdmFsb3IgTlVMTCwgYSBxdWVyeSBhYmFpeG8KIC0tIHJldG9ybmEgMTkgCiBTRUxFQ1QgQ09VTlQoSURBREUpIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gRElTVElOQ1QgY29udGEgb3MgdmFsb3JlcyBkaXN0aW50b3MgYXBlbmFzCiAtLSBhIHRhYmVsYSBwb3NzdWkgNTAwIHJlZ2lzdHJvcywgbWFzIGFwZW5hcyA2IHZhbG9yZXMKIC0tIGRpc3RpbnRvcyBubyBjYW1wbyBMT0dJTixsb2dvIG8gc2VsZWN0IGFiYWl4byByZXRvcm5hcsOhIDYKIFNFTEVDVCBDT1VOVChESVNUSU5DVCBMT0dJTikgRlJPTSBUQUJfQVVESVRPUklBX0xPR0lOCgogLS0gQUxMIGNvbnRhIHRvZG9zIG9zIHZhbG9yZXMgbsOjby1udWxvcyByZXBldGlkb3MKIFNFTEVDVCBDT1VOVChBTEwgTE9HSU4pIEZST00gVEFCX0FVRElUT1JJQV9MT0dJTg==[/et_pb_dmb_code_snippet]

1.2) SUM(E1)

Realiza uma soma dos valores de uma coluna numérica. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS0gc29tYSB0b2RvcyBvcyBzYWxhcmlvcwogU0VMRUNUIFNVTShTQUxBUklPKSBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]LS0gc29tYSB0b2RvcyBvcyBzYWxhcmlvcwogU0VMRUNUIFNVTShTQUxBUklPKSBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

1.3) MAX(E1) E MIN(E1)

Retorna, respectivamente o maior e o menor valor da coluna definida em E1. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIHJldG9ybmEgbyBtZW5vciBlIG1haW9yIHNhbGFyaW8gZGUgdG9kb3Mgb3MgZW1wcmVnYWRvcwogU0VMRUNUIE1JTihTQUxBUklPKSBBUyBNRU5PUl9TQUxBUklPLAogTUFYKFNBTEFSSU8pIEFTIE1BSU9SX1NBTEFSSU8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gcmV0b3JuYSBvIG1lbm9zIGUgbWFpb3Igc2Fsw6FyaW8gZGUgY2FkYSBkZXBhcnRhbWVudG8sIG91IHNlamEsCiAtLSBhZ3J1cGFkbyBwb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogTUlOKFNBTEFSSU8pIEFTIE1FTk9SX1NBTEFSSU8sCiBNQVgoU0FMQVJJTykgQVMgTUFJT1JfU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTw==” hover_enabled=”0″]IC0tIHJldG9ybmEgbyBtZW5vciBlIG1haW9yIHNhbGFyaW8gZGUgdG9kb3Mgb3MgZW1wcmVnYWRvcwogU0VMRUNUIE1JTihTQUxBUklPKSBBUyBNRU5PUl9TQUxBUklPLAogTUFYKFNBTEFSSU8pIEFTIE1BSU9SX1NBTEFSSU8KIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gcmV0b3JuYSBvIG1lbm9zIGUgbWFpb3Igc2Fsw6FyaW8gZGUgY2FkYSBkZXBhcnRhbWVudG8sIG91IHNlamEsCiAtLSBhZ3J1cGFkbyBwb3IgZGVwYXJ0YW1lbnRvCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogTUlOKFNBTEFSSU8pIEFTIE1FTk9SX1NBTEFSSU8sCiBNQVgoU0FMQVJJTykgQVMgTUFJT1JfU0FMQVJJTwogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTw==[/et_pb_dmb_code_snippet]

OBS1: Tanto o MIN quanto o MAX trabalham com tipos de dados numéricos, de data e caracteres.

OBS2: Uma atenção especial deve ser dada ao usar MIN/MAX com caracteres. A definição funciona da seguinte forma:

‘A’ é menor que ‘Z’;
‘Z’ é menos que ‘a’;
a string ‘2’ é maior que a string ‘100’

… ou seja, as letras maiusculas representam um “valor menor” que as letras minusculas. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIG5vIG1pbiwgbyBub21lIHJldG9ybmFkbyBjb21lw6dhIGNvbSBtYWnDunNjdWxhcwogLS0gbm8gbWF4LCBvIG5vbWUgcmV0b3JuYWRvIGNvbWXDp2EgY29tIG1pbnVzY3VsYXMKIFNFTEVDVCBNSU4oTk9NRV9FTVApLAogTUFYKE5PTUVfRU1QKQogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1M=” hover_enabled=”0″]IC0tIG5vIG1pbiwgbyBub21lIHJldG9ybmFkbyBjb21lw6dhIGNvbSBtYWnDunNjdWxhcwogLS0gbm8gbWF4LCBvIG5vbWUgcmV0b3JuYWRvIGNvbWXDp2EgY29tIG1pbnVzY3VsYXMKIFNFTEVDVCBNSU4oTk9NRV9FTVApLAogTUFYKE5PTUVfRU1QKQogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1M=[/et_pb_dmb_code_snippet]

1.4) AVG(E1)

Retorna a média dos valores definidos na coluna E1. Trabalha apenas com dados numéricos. Também ignora valores NULL. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIG1lZGlhIHNhbGFyaWFsIGRlIHRvZG9zIG9zIGVtcHJlZ2Fkb3MKIFNFTEVDVCBBVkcoU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS1tZWRpYSBzYWxhcmlhIGFncnVwYWRhIHBvciBkZXBhcnRhbWVudG8KIFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8sCiBBVkcoU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8=” hover_enabled=”0″]IC0tIG1lZGlhIHNhbGFyaWFsIGRlIHRvZG9zIG9zIGVtcHJlZ2Fkb3MKIFNFTEVDVCBBVkcoU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS1tZWRpYSBzYWxhcmlhIGFncnVwYWRhIHBvciBkZXBhcnRhbWVudG8KIFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8sCiBBVkcoU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8=[/et_pb_dmb_code_snippet]

OBS1: Pode-se usar as palavras DISTINCT e ALL com o AVG. O funcionamento é o mesmo do COUNT.

1.5) MEDIAN(E1)

Opera com tipos numéricos e de data, ignorando valores NULL. Retorna o valor que esta “no meio” de uma lista, ou seja a mediana. Por mediana, entende-se:

“mediana é uma medida de tendência central, um número que caracteriza as observações de uma determinada variável de tal forma que este número (a mediana) de um grupo de dados ordenados separa a metade inferior da amostra, população ou distribuição de probabilidade, da metade superior. Mais concretamente, 1/2 da população terá valores inferiores ou iguais à mediana e 1/2 da população terá valores superiores ou iguais à mediana.”
– fonte: Wikipedia

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIG1lZGlhbmEgZGUgdG9kb3Mgb3Mgc2FsYXJpb3MKIFNFTEVDVCBNRURJQU4oU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gbWVkaWFuYSBkZSBjYWRhIGRlcGFydGFtZW50bwogU0VMRUNUIElEX0RFUEFSVEFNRU5UTywKIE1FRElBTihTQUxBUklPKQogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTw==” hover_enabled=”0″]IC0tIG1lZGlhbmEgZGUgdG9kb3Mgb3Mgc2FsYXJpb3MKIFNFTEVDVCBNRURJQU4oU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCgogLS0gbWVkaWFuYSBkZSBjYWRhIGRlcGFydGFtZW50bwogU0VMRUNUIElEX0RFUEFSVEFNRU5UTywKIE1FRElBTihTQUxBUklPKQogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTw==[/et_pb_dmb_code_snippet]

1.6) RANK(C1) WITHIN GROUP (ORDER BY E1)

Os tipos de dados de C1 e E1 devem ser iguais. Essa função calcula o rank de um valor dentro de um grupo de valores. C1 é uma constante. A meu ver essa função é mais útil quando usada como função analítica. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIG1vc3RyYSBvIHJhbmsgZG8gc2FsYXJpbyBSJDIwMDAsMDAgZGVudHJvIGRvIHRvdGFsCiBTRUxFQ1QgUkFOSygyMDAwKSBXSVRISU4gR1JPVVAgKE9SREVSIEJZIFNBTEFSSU8pCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tIG1vc3RyYSBvIHJhbmsgZGUgY2FkYSBmdW5jaW9uw6FyaW8sIGJhc2VhZG8gZW0gc2V1cyBzYWzDoXJpb3MKIC0tIGZ1bsOnw6NvIGFuYWzDrXRpY2EKIFNFTEVDVCBJRF9FTVAsCiBOT01FX0VNUCwKIFJBTksoKSBPVkVSKE9SREVSIEJZIFNBTEFSSU8pCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]IC0tIG1vc3RyYSBvIHJhbmsgZG8gc2FsYXJpbyBSJDIwMDAsMDAgZGVudHJvIGRvIHRvdGFsCiBTRUxFQ1QgUkFOSygyMDAwKSBXSVRISU4gR1JPVVAgKE9SREVSIEJZIFNBTEFSSU8pCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwoKIC0tIG1vc3RyYSBvIHJhbmsgZGUgY2FkYSBmdW5jaW9uw6FyaW8sIGJhc2VhZG8gZW0gc2V1cyBzYWzDoXJpb3MKIC0tIGZ1bsOnw6NvIGFuYWzDrXRpY2EKIFNFTEVDVCBJRF9FTVAsCiBOT01FX0VNUCwKIFJBTksoKSBPVkVSKE9SREVSIEJZIFNBTEFSSU8pCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

1.7) DENSE_RANK

Calcula o rank de uma linha dentro de um grupo de linhas, ou seja, retorna a posição (rank) dessa linha dentro do grupo de linhas especificado pela query. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIGV4ZW1wbG8gZGUgdXNvIGRvIGRlbnNlX3JhbmsgY29tbyBmdW5jYW8gYWdyZWdhZGEKIC0tIGRhZG8gdW0gdmFsb3IsIGEgcXVlcnkgcmV0b3JuYSBhIHBvc2ljYW8gKHJhbmspIGRlc3NlIAogLS0gdmFsb3IsIGRlbnRybyBkYSB0YWJlbGEKIFNFTEVDVCBERU5TRV9SQU5LKDEwMDAwKSAKICAgIFdJVEhJTiBHUk9VUCAoT1JERVIgQlkgU0FMQVJJTyBERVNDKSBQT1NJQ0FPCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==” hover_enabled=”0″]IC0tIGV4ZW1wbG8gZGUgdXNvIGRvIGRlbnNlX3JhbmsgY29tbyBmdW5jYW8gYWdyZWdhZGEKIC0tIGRhZG8gdW0gdmFsb3IsIGEgcXVlcnkgcmV0b3JuYSBhIHBvc2ljYW8gKHJhbmspIGRlc3NlIAogLS0gdmFsb3IsIGRlbnRybyBkYSB0YWJlbGEKIFNFTEVDVCBERU5TRV9SQU5LKDEwMDAwKSAKICAgIFdJVEhJTiBHUk9VUCAoT1JERVIgQlkgU0FMQVJJTyBERVNDKSBQT1NJQ0FPCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUw==[/et_pb_dmb_code_snippet]

OBS1: A diferença entre o RANK e o DENSE_RANK é que o primeiro, ao detectar ranks repetidos, pula posições e o segundo, mantem o rank contínuo. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”ICAgICBSQU5LKCkgICAgICAgREVOU0VfUkFOSygpCiBWQUwgICAgICAgUkFOSyAgVkFMICAgICAgUkFOSwogLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLQogdmFsb3IxICAgIDEgICAgIHZhbG9yMSAgIDEKIHZhbG9yMiAgICAyICAgICB2YWxvcjIgICAyCiB2YWxvcjMgICAgMyAgICAgdmFsb3IzICAgMwogdmFsb3IzICAgIDMgICAgIHZhbG9yMyAgIDMKIHZhbG9yNCAgICA1ICAgICB2YWxvcjQgICA0″ hover_enabled=”0″]ICAgICBSQU5LKCkgICAgICAgREVOU0VfUkFOSygpCiBWQUwgICAgICAgUkFOSyAgVkFMICAgICAgUkFOSwogLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLQogdmFsb3IxICAgIDEgICAgIHZhbG9yMSAgIDEKIHZhbG9yMiAgICAyICAgICB2YWxvcjIgICAyCiB2YWxvcjMgICAgMyAgICAgdmFsb3IzICAgMwogdmFsb3IzICAgIDMgICAgIHZhbG9yMyAgIDMKIHZhbG9yNCAgICA1ICAgICB2YWxvcjQgICA0[/et_pb_dmb_code_snippet]

1.8) WM_CONCAT

Agrupa diversos valores de uma coluna em uma linha, separando as informações por vírgula. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”SU5TRVJUIElOVE8gQ0VSVElGSUNBQ0FPLlRBQl9HRU5FUklDQSAKICAgVkFMVUVTKCdSRUdJU1RSTyAwMScsICdERVNDUklDQU9fMDEnKTsKIElOU0VSVCBJTlRPIENFUlRJRklDQUNBTy5UQUJfR0VORVJJQ0EgCiAgIFZBTFVFUygnUkVHSVNUUk8gMDEnLCAnREVTQ1JJQ0FPXzAyJyk7CiBJTlNFUlQgSU5UTyBDRVJUSUZJQ0FDQU8uVEFCX0dFTkVSSUNBIAogICBWQUxVRVMoJ1JFR0lTVFJPIDAxJywgJ0RFU0NSSUNBT18wMycpOwogSU5TRVJUIElOVE8gQ0VSVElGSUNBQ0FPLlRBQl9HRU5FUklDQSAKICAgVkFMVUVTKCdSRUdJU1RSTyAwMScsICdERVNDUklDQU9fMDQnKTsKIENPTU1JVDsKIFNFTEVDVCBSRUdJU1RSTywgCiBXTV9DT05DQVQoREVTQ1JJQ0FPKSBBUyBERVNDCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfR0VORVJJQ0EKIEdST1VQIEJZIFJFR0lTVFJPCgogLS0tLS0KIHNhw61kYQogLS0tLS0KCiBSRUdJU1RSTyAgICBkZXNjCiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiBSRUdJU1RSTyAwMSBERVNDUklDQU9fMDEsREVTQ1JJQ0FPXzA0LERFU0NSSUNBT18wMyxERVNDUklDQU9fMDI=” hover_enabled=”0″]SU5TRVJUIElOVE8gQ0VSVElGSUNBQ0FPLlRBQl9HRU5FUklDQSAKICAgVkFMVUVTKCdSRUdJU1RSTyAwMScsICdERVNDUklDQU9fMDEnKTsKIElOU0VSVCBJTlRPIENFUlRJRklDQUNBTy5UQUJfR0VORVJJQ0EgCiAgIFZBTFVFUygnUkVHSVNUUk8gMDEnLCAnREVTQ1JJQ0FPXzAyJyk7CiBJTlNFUlQgSU5UTyBDRVJUSUZJQ0FDQU8uVEFCX0dFTkVSSUNBIAogICBWQUxVRVMoJ1JFR0lTVFJPIDAxJywgJ0RFU0NSSUNBT18wMycpOwogSU5TRVJUIElOVE8gQ0VSVElGSUNBQ0FPLlRBQl9HRU5FUklDQSAKICAgVkFMVUVTKCdSRUdJU1RSTyAwMScsICdERVNDUklDQU9fMDQnKTsKIENPTU1JVDsKIFNFTEVDVCBSRUdJU1RSTywgCiBXTV9DT05DQVQoREVTQ1JJQ0FPKSBBUyBERVNDCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfR0VORVJJQ0EKIEdST1VQIEJZIFJFR0lTVFJPCgogLS0tLS0KIHNhw61kYQogLS0tLS0KCiBSRUdJU1RSTyAgICBkZXNjCiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiBSRUdJU1RSTyAwMSBERVNDUklDQU9fMDEsREVTQ1JJQ0FPXzA0LERFU0NSSUNBT18wMyxERVNDUklDQU9fMDI=[/et_pb_dmb_code_snippet]

1.9) ROLLUP

É uma subclausula do GROUP BY que agrega dados agregados na saida de um SELECT. A saída é chamada de linhas superagregadas. Ele se beneficia melhor ao trabalhar com a função SUM, mas pode trabalhar com outras também. Ele pode ser usado para adicionar subtotais e totais para os grupos de linhas da query.

OBS1: Para cada N grupos, ROLLUP produz N + 1 agrupamentos.

Exemplo:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIG1vc3RyYSBhIHNvbWEgc2FsYXJpYWwsIHBvciBzZXRvciwKIC0tIHByb2R1emluZG8gdW0gc3VwZXJ0b3RhbCBubyBmaW5hbAogU0VMRUNUIElEX0RFUEFSVEFNRU5UTywKIFNVTShTQUxBUklPKQogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgCiBHUk9VUCBCWSBST0xMVVAoSURfREVQQVJUQU1FTlRPKQ==” hover_enabled=”0″]IC0tIG1vc3RyYSBhIHNvbWEgc2FsYXJpYWwsIHBvciBzZXRvciwKIC0tIHByb2R1emluZG8gdW0gc3VwZXJ0b3RhbCBubyBmaW5hbAogU0VMRUNUIElEX0RFUEFSVEFNRU5UTywKIFNVTShTQUxBUklPKQogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgCiBHUk9VUCBCWSBST0xMVVAoSURfREVQQVJUQU1FTlRPKQ==[/et_pb_dmb_code_snippet]

1.10) CUBE

É uma versão tridimensional do ROLLUP, pois calcula subtotais para cada agrupamento possível das colunas selecionadas e agrupadas. Ele produz 2n possíveis superagregações, se n colunas forem especificadas no GROUP BY.

Tanto o CUBE quanto o ROLLUP são maneiras eficiêntes de calcular totais e subtotais em um único SQL. A diferença entre o ROLLUP e o CUBE é que o ROLLUP executa um número limitado de calculos de subtotais, já o CUBE executa todas as combinações possíveis, incluíndo um grande total.

Exemplo:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIHByb2R1eiB1bSBzdWJ0b3RhbCBwYXJhIGNhZGEgY29taXNzYW8gZSB1bQogLS0gZ3JhbmRlIHRvdGFsCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogQ09NSVNTQU8sCiBTVU0oU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIAogR1JPVVAgQlkgQ1VCRShJRF9ERVBBUlRBTUVOVE8sIENPTUlTU0FPKQ==” hover_enabled=”0″]IC0tIHByb2R1eiB1bSBzdWJ0b3RhbCBwYXJhIGNhZGEgY29taXNzYW8gZSB1bQogLS0gZ3JhbmRlIHRvdGFsCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogQ09NSVNTQU8sCiBTVU0oU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIAogR1JPVVAgQlkgQ1VCRShJRF9ERVBBUlRBTUVOVE8sIENPTUlTU0FPKQ==[/et_pb_dmb_code_snippet]

1.11) GROUPING

O GROUPING identifica linhas superagregadas ou agregadas produzidas pelo ROLLUP/CUBE. Só é válido em uma clausula SELECT que use o GROUP BY

Quando se usar o GROUPING sem as funções ROLLUP/CUBE, ele sempre retornará 0

O GROUPING atribui 1 para as linhas superagregadas (produzidas pelo ROLLUP/CUBE) e 0 para as linhas agregadas. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIENBU0UgR1JPVVBJTkcoSURfREVQQVJUQU1FTlRPKQogV0hFTiAxIFRIRU4gJ1RPVEFMJwogRUxTRSBUT19DSEFSKElEX0RFUEFSVEFNRU5UTykKIEVORCBBUyBERVBBUlRBTUVOVE8sCiBTVU0oU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIAogR1JPVVAgQlkgUk9MTFVQKElEX0RFUEFSVEFNRU5UTyk=” hover_enabled=”0″]U0VMRUNUIENBU0UgR1JPVVBJTkcoSURfREVQQVJUQU1FTlRPKQogV0hFTiAxIFRIRU4gJ1RPVEFMJwogRUxTRSBUT19DSEFSKElEX0RFUEFSVEFNRU5UTykKIEVORCBBUyBERVBBUlRBTUVOVE8sCiBTVU0oU0FMQVJJTykKIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIAogR1JPVVAgQlkgUk9MTFVQKElEX0RFUEFSVEFNRU5UTyk=[/et_pb_dmb_code_snippet]

2) HAVING

Restringe os grupos identificados no GROUP BY. Cada linha retornada pela query, usando o GROUP BY representa um agrupamento de dados. O HAVING pode ser usado para filtrar esse agrupamento de dados. Creio que para entender melhor, basta testar e ver exemplos:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIHJldG9ybmEgYXBlbmFzIG9zIGRlcGFydGFtZW50b3MgcXVlIHBvc3N1YW0KIC0tIGEgc29tYSBkZSBzYWxhcmlvcyBtYWlvciBxdWUgUiQzNS4wMDAsMDAKIFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8sCiBTVU0oU0FMQVJJTykgQVMgVE9UQUxfU0FMQVJJT19ERVBUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyAKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTwogSEFWSU5HIFNVTShTQUxBUklPKSA+IDM1MDAwCiBPUkRFUiBCWSAx” hover_enabled=”0″]IC0tIHJldG9ybmEgYXBlbmFzIG9zIGRlcGFydGFtZW50b3MgcXVlIHBvc3N1YW0KIC0tIGEgc29tYSBkZSBzYWxhcmlvcyBtYWlvciBxdWUgUiQzNS4wMDAsMDAKIFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8sCiBTVU0oU0FMQVJJTykgQVMgVE9UQUxfU0FMQVJJT19ERVBUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyAKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTwogSEFWSU5HIFNVTShTQUxBUklPKSA+IDM1MDAwCiBPUkRFUiBCWSAx[/et_pb_dmb_code_snippet]

OBS1: O ORDER BY deve necessariamente vir por último na query.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”IC0tIGdlcmEgdW0gZXJybywgcG9pcyBuw6NvIHNlIGVzdGEgY29tcGFyYW5kbyB1bSB2YWxvciBhZ3JlZ2FkbywKIC0tIG1hcyBzaW0gdW0gdmFsb3IgYnJ1dG8KIFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8sCiBTVU0oU0FMQVJJTykgQVMgVE9UQUxfU0FMQVJJT19ERVBUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyAKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTwogSEFWSU5HIFNBTEFSSU8gPiAzNTAwMAogT1JERVIgQlkgMSAKCiAtLSDDqSBwb3Nzw612ZWwgdXNhciBvIEhBVklORyBjb20gc3VicXVlcmllcy4KIC0tIHN1YnF1ZXJpZXMgc2Vyw6NvIGFib3JkYWRhcyBlbSBwcsOzeGltb3MgdMOzcGljb3MKCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogU1VNKFNBTEFSSU8pIEFTIFRPVEFMX1NBTEFSSU9fREVQVAogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8KIEhBVklORyBBVkcoU0FMQVJJTykgPiAoU0VMRUNUIEFWRyhTQUxBUklPKSAKICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIFdIRVJFIElEQURFID4gNTApCiBPUkRFUiBCWSAx” hover_enabled=”0″]IC0tIGdlcmEgdW0gZXJybywgcG9pcyBuw6NvIHNlIGVzdGEgY29tcGFyYW5kbyB1bSB2YWxvciBhZ3JlZ2FkbywKIC0tIG1hcyBzaW0gdW0gdmFsb3IgYnJ1dG8KIFNFTEVDVCBJRF9ERVBBUlRBTUVOVE8sCiBTVU0oU0FMQVJJTykgQVMgVE9UQUxfU0FMQVJJT19ERVBUCiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyAKIEdST1VQIEJZIElEX0RFUEFSVEFNRU5UTwogSEFWSU5HIFNBTEFSSU8gPiAzNTAwMAogT1JERVIgQlkgMSAKCiAtLSDDqSBwb3Nzw612ZWwgdXNhciBvIEhBVklORyBjb20gc3VicXVlcmllcy4KIC0tIHN1YnF1ZXJpZXMgc2Vyw6NvIGFib3JkYWRhcyBlbSBwcsOzeGltb3MgdMOzcGljb3MKCiBTRUxFQ1QgSURfREVQQVJUQU1FTlRPLAogU1VNKFNBTEFSSU8pIEFTIFRPVEFMX1NBTEFSSU9fREVQVAogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgCiBHUk9VUCBCWSBJRF9ERVBBUlRBTUVOVE8KIEhBVklORyBBVkcoU0FMQVJJTykgPiAoU0VMRUNUIEFWRyhTQUxBUklPKSAKICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIFdIRVJFIElEQURFID4gNTApCiBPUkRFUiBCWSAx[/et_pb_dmb_code_snippet]
OBS2: É possível usar o WHERE e o HAVING na mesma QUERY. O WHERE
 filtra as linhas antes de serem agrupadas, já o HAVING as filtra 
 após o agrupamento.

É isso por enquanto pessoal. Até a próxima oportunidade. Keep Querying.