Aggregating Attributes I have a product review table in an oracle 12.1 EE database that has different attributes rated on a scale of 1 through 5. How do I query this table to count up all of the reviews and group by the product and the 1-5 rating.

Example Data

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
SELECT  *
FROM    DATA_SET;

review_id | product_id | overall | comfort | fit   | appearance
----------------------------------------------------------------
1            a            5         4         5       4
2            a            4         4         4       4
3            b            4         5         4       5
4            c            3         2         2       4
5            c            2         1         2       1



I've looked at doing pivots and various analytic queries. But I can't quite get the desired output below. I'm sure I'm making this too hard. But for the life of me I can't put this together. Any help/direction would be appreciated!

Example Output

product_id | rating | overall | comfort | fit   | appearance
--------------------------------------------------------------
a            1         0        0         0         0
a            2         0        0         0         0
a            3         0        0         0         0
a            4         1        2         1         2
a            5         1        0         1         0
b            1         0        0         0         0
b            2         0        0         0         0
b            3         0        0         0         0
b            4         1        0         1         1
b            5         0        1         0         0
c            1         0        1         0         1
c            2         1        1         2         0
c            3         1        0         0         0
c            4         0        0         0         1
c            5         0        0         0         0
HOWTO

Your query needs to do the following:


UNPIVOT the data
PIVOT the data to get the COUNT()
add missing rows


sub-query of all row LEFT OUTER JOIN calculated data
use of then not-well-known MODEL clause

and, of course, the ORDER BY clause


EXAMPLE 1 (sub-query)

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
,unpivot_data as (
  SELECT  product_id, score, catagory
  FROM    DATA_SET
    UNPIVOT ( score
              for catagory in (
                overall, comfort, fit, appearance
              )
            )
  )
,count_values as (  
  select product_id, score, overall, comfort, fit, appearance
  from unpivot_data
  pivot (
    count(catagory)
    for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
  )
)
,all_rows as (
  select *
  from (select distinct product_id from data_set),
       (select level as score from dual connect by level &lt;= 5)
)
select a.product_id, a.product_id
  ,nvl( b.overall, 0) overall
  ,nvl( b.comfort, 0) comfort
  ,nvl( b.fit, 0) fit
  ,nvl( b.appearance, 0) appearance
from all_rows a
  left outer join count_values b
    on (a.product_id=b.product_id and a.score=b.score)
order by a.product_id, a.score


EXAMPLE 2 (model)

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
,unpivot_data as (
  SELECT  product_id, score, catagory
  FROM    DATA_SET
    UNPIVOT ( score
              for catagory in (
                overall, comfort, fit, appearance
              )
            )
  )
,count_values as (  
  select product_id, score, overall, comfort, fit, appearance
  from unpivot_data
  pivot (
    count(catagory)
    for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
  )
)
select *
from count_values
model return all rows
  DIMENSION by (product_id,score)
  measures ( overall, comfort, fit, appearance )
  rules (
    overall[ for product_id in ( select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( overall[ cv(product_id), cv(score) ], 0),
    comfort[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( comfort[ cv(product_id), cv(score) ], 0),
    fit[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( fit[ cv(product_id), cv(score) ], 0),
    appearance[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( appearance[ cv(product_id), cv(score) ], 0)
    )
order by product_id, score