I have a a below table where all the columns are same except for group column and I am calculating count(distinct group) and blocks in the same table:
Input:
id | time | CODE | group | value | total_blocks |
---|---|---|---|---|---|
1 | 22 | 32206 | mn2 | 1 | 200 |
1 | 22 | 32206 | mn4 | 1 | 200 |
Output:
id | time | CODE | group | value | count(distinct group) | blocks |
---|---|---|---|---|---|---|
1 | 22 | 32206 | mn2 | 1 | 2 | 100 |
1 | 22 | 32206 | mn4 | 1 | 2 | 100 |
count(distinct group) is just distinct values (mn2 and mn4) and blocks overall wrt to code(32206) is 200, but I am splitting the same over the two rows. The output should look exactly the same in the final, without removal of any columns.
I tried using count(distinct) but it didn't work
CodePudding user response:
Oracle:
Try it like here:
WITH -- S a m p l e d a t a
tbl (ID, A_TIME, CODE, A_GROUP, A_VALUE, TOTAL_BLOCKS) AS
(
Select 1, 22, 32206, 'mn2', 1, 200 From Dual Union All
Select 1, 22, 32206, 'mn4', 1, 200 From Dual
)
-- S Q L --
Select
ID, A_TIME, CODE, A_GROUP, A_VALUE,
Count(DISTINCT A_GROUP) OVER(Partition By CODE) "COUNT_DIST_GROUP", -- Count distinct groups per CODE
-- Count(DISTINCT A_GROUP) OVER() "COUNT_DIST_GROUP", --Count distinct groups over the whole table
TOTAL_BLOCKS / Count(*) OVER(Partition By CODE) "BLOCKS" -- TOTAL_BLOCKS divided by number of rows per CODDE
-- TOTAL_BLOCKS / Count(*) OVER() "BLOCKS" -- TOTAL_BLOCKS divided by number of rows in the whole table
From
tbl
R e s u l t :
ID A_TIME CODE A_GROUP A_VALUE COUNT_DIST_GROUP BLOCKS
---------- ---------- ---------- ------- ---------- ---------------- ----------
1 22 32206 mn2 1 2 100
1 22 32206 mn4 1 2 100
Comments in code explain the basic use of Count() Over() analytic function. More about analytic functions here.
Using just ROW_NUMBER() analytic function and Max() aggregate function...
-- S Q L --
Select
r.ID, r.A_TIME, r.CODE, t.A_GROUP, r.A_VALUE, MAX_RN "COUNT_DIST_GROUP", (TOTAL_BLOCKS / MAX_RN) "BLOCKS"
From
( SELECT ID, A_TIME, CODE, A_VALUE, Max(RN) "MAX_RN"
FROM (Select ID, A_TIME, CODE, A_VALUE, Row_Number() OVER(Partition By CODE Order By CODE, A_GROUP) "RN"
From tbl )
GROUP BY ID, A_TIME, CODE, A_VALUE ) r
Inner Join tbl t ON(t.CODE = r.CODE)
ID A_TIME CODE A_GROUP A_VALUE COUNT_DIST_GROUP BLOCKS
---------- ---------- ---------- ------- ---------- ---------------- ----------
1 22 32206 mn2 1 2 100
1 22 32206 mn4 1 2 100
... and it works with another group of similar data:
WITH -- S a m p l e d a t a
tbl (ID, A_TIME, CODE, A_GROUP, A_VALUE, TOTAL_BLOCKS) AS
(
Select 1, 22, 32206, 'mn2', 1, 200 From Dual Union All
Select 1, 22, 32206, 'mn4', 1, 200 From Dual Union All
--
Select 1, 22, 32207, 'mn6', 1, 450 From Dual Union All
Select 1, 22, 32207, 'mn7', 1, 450 From Dual Union All
Select 1, 22, 32207, 'mn8', 1, 450 From Dual
)
-- S Q L --
Select
r.ID, r.A_TIME, r.CODE, t.A_GROUP, r.A_VALUE, MAX_RN "COUNT_DIST_GROUP", (TOTAL_BLOCKS / MAX_RN) "BLOCKS"
From
( SELECT ID, A_TIME, CODE, A_VALUE, Max(RN) "MAX_RN"
FROM (Select ID, A_TIME, CODE, A_VALUE, Row_Number() OVER(Partition By CODE Order By CODE, A_GROUP) "RN"
From tbl )
GROUP BY ID, A_TIME, CODE, A_VALUE ) r
Inner Join tbl t ON(t.CODE = r.CODE)
ID A_TIME CODE A_GROUP A_VALUE COUNT_DIST_GROUP BLOCKS
---------- ---------- ---------- ------- ---------- ---------------- ----------
1 22 32206 mn2 1 2 100
1 22 32206 mn4 1 2 100
1 22 32207 mn6 1 3 150
1 22 32207 mn7 1 3 150
1 22 32207 mn8 1 3 150