Início / dba / Perguntas / 139023
Accepted
Hector
Asked: 2016-05-20 07:58:46 +0800 CST

ORACLE: Alterando o agrupamento de dados para basear-se nos detalhamentos do mês

  • 772

Para manter isso curto e direto ao ponto, estou procurando modificar o agrupamento para que essa consulta SQL vá de NAMEparaMonth/yy

Aqui está um exemplo do script e capturas de tela do arquivo de saída

Isto é o que estou tentando realizar.

Alguma ideia de como abordar isso?

Script SQL:

SELECT A.NAME
     COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END)) AS OCT11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END) AS OCT11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END)) AS OCT11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END) AS OCT11_INV_LINES
-- NOV 11 
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END)) AS NOV11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END) AS NOV11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END)) AS NOV11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END) AS NOV11_INV_LINES
-- DEC 11     
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END)) AS DEC11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END) AS DEC11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END)) AS DEC11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END) AS DEC11_INV_LINES

FROM test.test1 A 
WHERE TO_DATE(A.INVDATE) BETWEEN TO_DATE('01-OCT-11') AND TO_DATE('30-SEP-12')
AND COMPANY = '30'
AND CUST_GRP IN ('3','4')
GROUP BY A.NAME

ARQUIVO DE SAÍDA :Arquivo de saída atual

SAÍDA DESEJADA Formato de saída ideal

Para a saída desejada, todas as colunas devem sercounts

Obrigado!

oracle select

1 respostas

  1. Best Answer

    Eu descobri, tive que group by trucn(invdate,'mm')e countas colunas separadas

    SELECT TO_CHAR(TRUNC(inv.invdate,'MM'),'MON-RR') "Month",
         COUNT(DISTINCT(inv.order_no)) "Orders",
         COUNT(inv.order_no) "Order Lines",
         COUNT(DISTINCT(inv.invoice_no)) "Invoices",
         COUNT(inv.invoice_no) "Invoice Lines",
         COUNT(DISTINCT(inv.name)) "Companies"
FROM test.test1 inv
GROUP BY TRUNC(inv.invdate,'MM')

    Saída de dados para consulta

    • 1

relate perguntas