Skip to Main Content

PIVOT vs GROUP BY

user13117585Apr 5 2019 — edited Apr 6 2019

Hello everyone,

I have a few questions about a PIVOT query I'm trying to write. Imagine the following basic scenario.

CREATE TABLE src

(

  id NUMBER(10),

  creation_date DATE,

  grp VARCHAR(10)

);

insert into src(id, creation_date, grp)

SELECT level,

       TO_DATE('01/01/2019', 'DD/MM/YYYY') + ROUND(DBMS_RANDOM.VALUE (1, SYSDATE - TO_DATE('01/01/2019', 'DD/MM/YYYY'))), 

       'A' || ROUND(DBMS_RANDOM.VALUE (1, 9))

  FROM dual

CONNECT BY LEVEL <= 100;

COMMIT;

I have tried to group by grp and have a sum per week in a different column each. I have created the following query.

WITH get_counts AS

(

SELECT TO_CHAR(creation_date, 'WW') AS week_number,

       grp,

       COUNT(id) AS cnt

  FROM src

GROUP BY TO_CHAR(creation_date, 'WW'), grp

)

SELECT *

  FROM get_counts c

PIVOT (

   SUM(cnt)

   FOR week_number IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)

)

ORDER BY grp

;

But for sure, something is fishy. But I don't see what. Can anyone help me understand my mistakes?

This query works a little bit better.

SELECT grp,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 1 THEN id END) AS cnt_01,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 2 THEN id END) AS cnt_02,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 3 THEN id END) AS cnt_03,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 4 THEN id END) AS cnt_04,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 5 THEN id END) AS cnt_05,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 6 THEN id END) AS cnt_06,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 7 THEN id END) AS cnt_07,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 8 THEN id END) AS cnt_08,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 9 THEN id END) AS cnt_09,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 10 THEN id END) AS cnt_10,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 11 THEN id END) AS cnt_11,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 12 THEN id END) AS cnt_12,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 13 THEN id END) AS cnt_13,

       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 13 THEN id END) AS cnt_14

  FROM src

GROUP BY grp ;

I has the right totals. But, not of these two queries are what I need. I'm trying to have ONLY the last 10 weeks. So, the colum name should reflect the correct week number. Is this possible with Oracle 11g or 12c?

Regards,

Comments
Post Details
Added on Apr 5 2019
14 comments
586 views