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:

Output:

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