Forum Stats

  • 3,875,419 Users
  • 2,266,914 Discussions
  • 7,912,203 Comments

Discussions

GROUP BY result is not seems right

User_ZR3PE
User_ZR3PE Member Posts: 20 Green Ribbon
edited Nov 18, 2022 11:08AM in SQL & PL/SQL

I do not understand how below query results turn empty rows for "SORGUKANALI" and "TAHSILEDILENTOPLAMMASRAF" because when loading data I choose not null data for "SORGUKANALI" and "TAHSILEDILENTOPLAMMASRAF". Source Data is in the attachments.

QUERY:

SELECT T.SORGUKANALI,

     T.TAHSILEDILENTOPLAMMASRAF, 

     SUM(T.TAHSILEDILENMASRAF)

 FROM (SELECT *

      FROM EXPORTSAMPLETABLE

      WHERE TRANSACTIONCODE = 'HVLFHSBHVL'

       and rownum <= 100

       and EXPORTSAMPLETABLE.SORGUKANALI is not null

       and EXPORTSAMPLETABLE.TAHSILEDILENTOPLAMMASRAF is not null) T

 GROUP BY ROLLUP(T.SORGUKANALI, T.TAHSILEDILENTOPLAMMASRAF);

QUERY RESULT:



Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond
    edited Nov 18, 2022 11:31AM Answer ✓

    Hi, @User_ZR3PE

    I do not understand how below query results turn empty rows for "SORGUKANALI" and "TAHSILEDILENTOPLAMMASRAF" 

    There are no empty rows. If you look closely, you';ll notice that all 6 rows have a number in the last column. Do you mean rows where the sorgukanali and tahsiledilentoplammasraf are empty? Those are the Super-Aggregate rows created by ROLLUP. When you say

     GROUP BY ROLLUP(T.SORGUKANALI, T.TAHSILEDILENTOPLAMMASRAF);

    that means produce

    • one row for each distinct combination of sorgukanali and tahsiledilentoplammasraf (rownums 1, 2 and 6 in your output) plus
    • one suer-aggregate row for each distinct value of sorgukanali (rownums 3 and 5), which include all values of tahsiledilentoplammasraf, so the tahsiledilentoplammasraf column is NULL, and
    • one row for all values of sorgukanali and tahsiledilentoplammasraf (rownum 6)

    Notice that the SUMS in rownum 3 is the total of the SUMs in rownums 1-2 (45.22 = 45.22 + 0.00) and rownum 5 is the total of rownum 4 (0.00 = 0.00) and the grand total on rownum 6 is the total of rownums 3 and 5 (45.22 = 45.22 + 0.00).

    Source Data is in the attachments.

    Post the sample data right in this space. Don't use attachments. Not everyone can or will open attachments.

    User_ZR3PE
  • Paulzip
    Paulzip Member Posts: 8,808 Blue Diamond

    You're performing a rollup. That means subtotal and grandtotal of SUM(T.TAHSILEDILENMASRAF) on combinations of T.SORGUKANALI, T.TAHSILEDILENTOPLAMMASRAF. The nulls are those scenarios.

    User_ZR3PE

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond
    edited Nov 18, 2022 11:31AM Answer ✓

    Hi, @User_ZR3PE

    I do not understand how below query results turn empty rows for "SORGUKANALI" and "TAHSILEDILENTOPLAMMASRAF" 

    There are no empty rows. If you look closely, you';ll notice that all 6 rows have a number in the last column. Do you mean rows where the sorgukanali and tahsiledilentoplammasraf are empty? Those are the Super-Aggregate rows created by ROLLUP. When you say

     GROUP BY ROLLUP(T.SORGUKANALI, T.TAHSILEDILENTOPLAMMASRAF);

    that means produce

    • one row for each distinct combination of sorgukanali and tahsiledilentoplammasraf (rownums 1, 2 and 6 in your output) plus
    • one suer-aggregate row for each distinct value of sorgukanali (rownums 3 and 5), which include all values of tahsiledilentoplammasraf, so the tahsiledilentoplammasraf column is NULL, and
    • one row for all values of sorgukanali and tahsiledilentoplammasraf (rownum 6)

    Notice that the SUMS in rownum 3 is the total of the SUMs in rownums 1-2 (45.22 = 45.22 + 0.00) and rownum 5 is the total of rownum 4 (0.00 = 0.00) and the grand total on rownum 6 is the total of rownums 3 and 5 (45.22 = 45.22 + 0.00).

    Source Data is in the attachments.

    Post the sample data right in this space. Don't use attachments. Not everyone can or will open attachments.

    User_ZR3PE
  • Paulzip
    Paulzip Member Posts: 8,808 Blue Diamond

    You're performing a rollup. That means subtotal and grandtotal of SUM(T.TAHSILEDILENMASRAF) on combinations of T.SORGUKANALI, T.TAHSILEDILENTOPLAMMASRAF. The nulls are those scenarios.

    User_ZR3PE
  • User_ZR3PE
    User_ZR3PE Member Posts: 20 Green Ribbon

    Thanks for explanation now it makes sense

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond

    Another way of looking at this:

    SELECT    deptno, job, SUM (sal) AS total_sal
    FROM	  scott.emp
    GROUP BY  ROLLUP (deptno, job)
    ;
    

    gets the same results as

    SELECT    deptno, job, SUM (sal) AS total_sal
    FROM	  scott.emp
    GROUP BY  deptno, job
        UNION ALL
    SELECT    deptno, NULL, SUM (sal) AS total_sal
    FROM	  scott.emp
    GROUP BY  deptno, NULL	-- equivalent to   GROUP BY deptno
        UNION ALL
    SELECT    NULL, NULL, SUM (sal) AS total_sal
    FROM	  scott.emp
    GROUP BY  NULL, NULL	-- equivalent to not having any GROUP BY clause at all
    ;
    

    Both produce

    DEPTNO JOB       TOTAL_SAL
    ------- --------- ---------
         10 CLERK          1300
         10 MANAGER        2450
         10 PRESIDENT      5000
         10                8750
         20 CLERK          1900
         20 ANALYST        6000
         20 MANAGER        2975
         20               10875
         30 CLERK           950
         30 MANAGER        2850
         30 SALESMAN       5600
         30                9400
                          29025
    
    User_ZR3PE
  • User_ZR3PE
    User_ZR3PE Member Posts: 20 Green Ribbon

    thanks again for new explaination to 👏👏👏

  • mathguy
    mathguy Member Posts: 10,912 Black Diamond

    To get an even better understanding of how this rollup business works, you can use additional functions available when you aggregate in this way. There are a few related functions; for now, just look at GROUPING_ID(column):

    select deptno, job, grouping_id(deptno) as grp_id_dept,
                        grouping_id(job)    as grp_id_job , sum (sal) as total_sal
    from     scott.emp
    group  by rollup (deptno, job)
    ;
    
    DEPTNO JOB       GRP_ID_DEPT GRP_ID_JOB  TOTAL_SAL
    ------ --------- ----------- ---------- ----------
        10 CLERK               0          0       1300
        10 MANAGER             0          0       2450
        10 PRESIDENT           0          0       5000
        10                     0          1       8750
        20 CLERK               0          0       1900
        20 ANALYST             0          0       6000
        20 MANAGER             0          0       2975
        20                     0          1      10875
        30 CLERK               0          0        950
        30 MANAGER             0          0       2850
        30 SALESMAN            0          0       5600
        30                     0          1       9400
                               1          1      29025
    

    The rows where the JOB value is NULL are exactly the rows where GROUPING_ID(job) is 1; there are rows where partial sums (rollups) are shown in the TOTAL_SAL column. The partial sums are for all jobs in each distinct department. The last row in the output has both GROUPING_ID(job) and GROUPING_ID(deptno) equal to 1. This indicates that now the sum aggregates for all jobs in all departments.

    These GROUPING_ID() (and other closely related) functions can be used to great benefit in the actual writing of queries - they do useful things; they are not just for "learning", even though we can also use them for "learning" as I demonstrated here. Usually you wouldn't include the GROUPING_ID values in the output (other than while you are developing a query, to help you see what is happening, and to help with debugging), but you would use them in expressions in the SELECT clause and perhaps in ORDER BY, etc.

    User_ZR3PE
  • User_ZR3PE
    User_ZR3PE Member Posts: 20 Green Ribbon

    thanks for explanation