I have been struggling on how I can achieve the below output:

Background: Range is the number of items within ProductA and ProductB. They both have two different number of items. ProductA usually falls into different range category then ProductB. I need to get one range for ProductA and ProductB.

Table should be read as : e.g. ProductA has 34 number of items and falls into range 30-40, but ProductB (67 number of items) falls into different range in this case 60-70.

Here is the code - I need to find a way to get one range category(currently I have two separately: for ProductA and ProductB) that will be assigned for ProductA and ProductB and will give me one range for both:

WITH ProductA AS (
    select
        distinct m.ProductA as product_count_A,
        case
            when utilizationA <= 10 THEN '0-10'
            when utilizationA <= 20
            AND utilizationA > 10 THEN '10-20'
            when utilizationA <= 30
            AND utilizationA > 20 THEN '20-30'
            when utilizationA <= 40
            AND utilizationA > 30 THEN '30-40'
            when utilizationA <= 50
            AND utilizationA > 40 THEN '40-50'
            when utilizationA <= 60
            AND utilizationA > 50 THEN '50-60'
            when utilizationA <= 70
            AND utilizationA > 60 THEN '60-70'
            when utilizationA <= 80
            AND utilizationA > 70 THEN '70-80'
            when utilizationA <= 90
            AND utilizationA > 80 THEN '80-90'
            when utilizationA <= 100
            AND utilizationA > 90 THEN '90-100'
        END "UtilizationA"
    from
        PRODUCTS_TABLE
),
ProductB AS (
    Select
        distinct ProductB as product_count_B,
        CASE
            when utilizationB <= 10 THEN '0-10'
            when utilizationB <= 20
            AND utilizationB > 10 THEN '10-20'
            when utilizationB <= 30
            AND utilizationB > 20 THEN '20-30'
            when utilizationB <= 40
            AND utilizationB > 30 THEN '30-40'
            when utilizationB <= 50
            AND utilizationB > 40 THEN '40-50'
            when utilizationB <= 60
            AND utilizationB > 50 THEN '50-60'
            when utilizationB <= 70
            AND utilizationB > 60 THEN '60-70'
            when utilizationB <= 80
            AND utilizationB > 70 THEN '70-80'
            when utilizationB <= 90
            AND utilizationB > 80 THEN '80-90'
            when utilizationB <= 100
            AND utilizationB > 90 THEN '90-100'
        END "UtilizationB"
    from
        PRODUCTS_TABLE
)
SELECT
    DISTINCT "UtilizationA",
    "UtilizationB",
    COUNT(ProductB.product_count_B) as ProductB_RES,
    COUNT(ProductA.product_count_A) as ProductA_RES
from
    ProductB,
    ProductA
GROUP BY
    "UtilizationA",
    "UtilizationB";

CodePudding user response：

UNPIVOT the columns to rows and convert the utilization to ranges and then PIVOT back from rows to columns:

SELECT a_product AS productA,
       b_product AS productB,
       utilization
FROM   (
  SELECT type,
         CASE
         WHEN utilization <= 10 THEN '0-10'
         WHEN utilization <= 20 THEN '10-20'
         WHEN utilization <= 30 THEN '20-30'
         WHEN utilization <= 40 THEN '30-40'
         WHEN utilization <= 50 THEN '40-50'
         WHEN utilization <= 60 THEN '50-60'
         WHEN utilization <= 70 THEN '60-70'
         WHEN utilization <= 80 THEN '70-80'
         WHEN utilization <= 90 THEN '80-90'
         ELSE '90-100'
         END AS utilization,
         product
  FROM   products_table
  UNPIVOT (
    (product, utilization) FOR type IN (
      (producta, utilizationa) AS 'A',
      (productb, utilizationb) AS 'B'
    )
  )
)
PIVOT (
  COUNT(DISTINCT product) AS product
  FOR type IN (
    'A' AS a,
    'B' AS b
  )
)

Which, for the sample data:

CREATE TABLE products_table(producta, utilizationa, productb, utilizationb) AS
SELECT 1,  1, 1,  1 FROM DUAL UNION ALL
SELECT 1, 11, 2,  1 FROM DUAL UNION ALL
SELECT 1, 21, 1, 11 FROM DUAL UNION ALL
SELECT 1, 31, 1, 21 FROM DUAL UNION ALL
SELECT 1, 41, 1, 21 FROM DUAL UNION ALL
SELECT 1, 51, 1, 51 FROM DUAL UNION ALL
SELECT 1, 61, 1, 71 FROM DUAL UNION ALL
SELECT 1, 71, 1, 81 FROM DUAL;

Outputs:

PRODUCTA	PRODUCTB	UTILIZATION
1	2	0-10
1	1	10-20
1	1	20-30
1	0	30-40
1	0	40-50
1	1	50-60
1	0	60-70
1	1	70-80
0	1	80-90

db<>fiddle here

CodePudding user response：

As I have some background on Business Intelligence this looks similarly to problem I sometimes had on my reports: we tried to merge cpu p95 with cpu_max utilization to count how many hosts are eligible to one group/range set by p95 and separate count, but the same grouping, for cpu_max in other column.

Here's my take on this: http://sqlfiddle.com/#!18/babfb/1/0

Simply I am preparing two sets of counts and their assignements to appropriate ranges to finally JOIN them by the rank on the resulting query.