ORACLE: Changing the grouping of a data to be based on month breakdowns

countgroup byoracleselect

To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy

Here is a example of the script, and screenshots of the output file

This is what I am trying to accomplish.

Any ideas on how to approach this?

SQL SCRIPT:

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 

OUTPUT FILE: Current Output file

DESIRED OUTPUT Ideal Output format

For the desired output, all of the columns should be counts

Thanks!

Best Answer

I figured it out, had to group by trucn(invdate,'mm') and count the separate columns

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')

Data output for query