1 2 Previous Next 16 Replies Latest reply: Feb 26, 2009 11:30 AM by Frank Kulash RSS

    GROUP BY clause with Alias

    633450
      Hi,
      I am trying to run following query and i tried to run littel different way but no luck.
      If someone can help me out.
      I know that icann't use the alias in GROUP BY clause.
      I am getting errors like "NOT a GROUP BY Function" or Identifier is not valid within my different fromat of the sql.
      For C_CODE (MAx function part_, i have to make a header for each C__CODE and put the corrospondence value for S_TASK.


      SELECT  s_name ,login, f_name, l_name, email, S_ID, S_EMAIL,
      desc, wave,

      MAX(DECODE(C_CODE, '100',S_TASK, '')) "100",
      MAX(DECODE(C_CODE, '150',S_TASK, '')) "150",
      MAX(DECODE(C_CODE, '200',S_TASK, '')) "200"
      FROM

      *(*
      SELECT  b.S_name, d.login, d.f_name,d.l_name, d.email,
      *(SELECT d.login FROM user d WHERE d.id = c.rep_to_id) AS S_ID,*
      *(SELECT d.email FROM user d WHERE d.id = c.rep_to_id) AS S_EMAIL,*
      e.desc, f.wave, C_CODE, S_TASK
      FROM CT_E_STAK_MAP       A,
      ct_stak           B,
      e_info           C,
      user                D,
      e_code           E,
      e_web_info           F,

      C_STAK_MAP           G,
      C_CORS           H,

      C_S_TASK           I,
      C_S_STAK_MAP           J
      WHERE a.C_stak_id      = b.c_stak_id

      AND   b.c_stak_id      = G.c_stak_id
      AND   B.C_STAK_ID      = J.C_STAK_ID
      AND   J.C_S_TASK_ID      = I.C_S_TASK_ID
      AND   G.C_CORS_ID           = H.C_CORS_ID
      AND   a.U_id              = c.U_id

      AND   a.U_id              = d.id

      AND   d.id              = f.U_id

      AND   c.ctll3           = e.c_st_val

      AND   c.ctl1_2           = e.ctl1_2

      AND   e.c_set_name      = 'EY'

      AND   c.active          = 1
      GROUP BY b.s_name, d.login, d.f_name,d.l_name, d.email,d.login, d.email, S_ID, S_EMAIL,
      e.desc, f.wave, C_CODE, S_TASK
      *)*
      GROUP BY s_name ,login, f_name, l_name, email, S_ID, S_EMAIL, desc, wave

      Please let me know if you need more info.
      Thanks for your help!
        • 1. Re: GROUP BY clause with Alias
          SomeoneElse
          Can you remove the group by from the inner query?

          And please use
           tags to post code.  This is kind of unreadable.
          
          Instructions are here: http://forums.oracle.com/forums/help.jspa                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: GROUP BY clause with Alias
            633450
            Thanks for your prompt resposne.
            Sorry about not using code tagas i tried to make it bold but forgot to use code tag.
            If i remove group by part then where i should use?

            Thanks,
            • 3. Re: GROUP BY clause with Alias
              SomeoneElse
              If i remove group by part then where i should use?
              Remove the group by from the inner query and see if it gives you what you want.
              • 4. Re: GROUP BY clause with Alias
                633450
                Thank you very much!
                It works!
                Appreciated your help!

                Regards,
                • 5. Re: GROUP BY clause with Alias
                  633450
                  Hi,
                  That tips works but user wants some headers for couple columns value and i never done as i always hard coded the header from the field values.
                  So instead of following:
                  MAX(DECODE(C_CODE, '100',S_TASK, '')) "100",
                  MAX(DECODE(C_CODE, '150',S_TASK, '')) "150",
                  MAX(DECODE(C_CODE, '200',S_TASK, '')) "200"
                  I want C_CODE value dynamically as user sometimes add and this report run everyday as nightly.

                  Ex.
                  If C_CODE value is what ever for that user i wants to show that value as header.

                  Thanks for your help!
                  thanks,
                  • 6. Re: GROUP BY clause with Alias
                    SomeoneElse
                    show that value as header.
                    Show in what? Using what reporting tool?

                    There's no easy way to change the column alias inside the select itself. You'd have to use dynamic sql.
                    • 7. Re: GROUP BY clause with Alias
                      Sven W.
                      Displaying headers is mostly the task of the output tool. You didn#t mention what tool you use.

                      A workaround can be to create an extra line that holds the values that you want to use.

                      Eg
                      select empno, ename
                      from
                         (select 2 sortorder, to_char(empno) empno, ename from emp /* many data records */
                         UNION ALL
                         select 1 sortorder, 'empno' empno, 'ename' ename from dual /* header record */
                         UNION ALL
                         select 3 sortorder, to_char(count(*)) empno, 'number of employees' ename from emp /* footer record */
                         )
                      order by sortorder, to_number(empno);
                      • 8. Re: GROUP BY clause with Alias and Column Header
                        633450
                        Thanks.
                        I am using sql plus not any other tool.
                        • 9. Re: GROUP BY clause with Alias and Column Header
                          SomeoneElse
                          You might be able to accomplish this with substitution variables (or command line variables) in SQL Plus.

                          Just use &1, &2, etc. for each hardcoded value. Then supply those on the command line when you call SQL Plus.

                          This will even work for column aliases. But the alias must follow Oracle naming conventions and not be just a number. But you could give it a prefix.

                          Something like this:
                          MAX(DECODE(C_CODE, '&1',S_TASK, '')) code_&1,
                          MAX(DECODE(C_CODE, '&2',S_TASK, '')) code_&2,
                          MAX(DECODE(C_CODE, '&3',S_TASK, '')) code_&3
                          • 10. Re: GROUP BY clause with Alias and Column Header
                            633450
                            Thanks Swen.
                            I appolize if its coded properly as i tried to use but couldn't see the code tag.
                            I don't understand if you explain me little further as i ran your query and get the result like:
                            EMPNO ENAME
                            -----
                            -------------------
                            empno ename
                            7499 ALLEN
                            7566 JONES
                            7654 MARTIN
                            7658 CHAN
                            7782 CLARK
                            7839 KING
                            7934 MILLER
                            8000 MIKE
                            8001 prs
                            9 number of employees
                            
                            11 rows selected.
                            
                            [/CODE] 
                            I like result as:
                            [CODE]
                            Fname Lname email add1 add2 city state zip ename dept1 dept2 dept3 ...
                            p          s         [p@email.com|mailto:p@email.com] 123  niles IL 61000 PS  FIN   A/C   null       
                            [/CODE]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            • 11. Re: GROUP BY clause with Alias and Column Header
                              Frank Kulash
                              Hi,

                              In SQL*Plus, you can do a preliminary query to find the variable parts (in your case, find out all the distinct values for c_code), and store the results in a substitution variable or a file, to be used in the main query.

                              The following shows how to do that using a SPOOL file.
                              /*
                              How to Pivot a Table with a Dynamic Number of Columns
                              
                              This works in any version of Oracle
                              The "SELECT ... PIVOT" feature introduced in Oracle 11
                              is much better for producing XML output.
                              
                              Say you want to make a cross-tab output of
                              the scott.emp table.
                              Each row will represent a department.
                              There will be a separate column for each job.
                              Each cell will contain the number of employees in
                                   a specific department having a specific job.
                              The exact same solution must work with any number
                              of departments and columns.
                              (Within reason: there's no guarantee this will work if you 
                              want 2000 columns.)
                              
                              Case 0 "Basic Pivot" shows how you might hard-code three
                              job types, which is exactly what you DON'T want to do.
                              Case 1 "Dynamic Pivot" shows how get the right results
                              dynamically, using SQL*Plus.  
                              (This can be easily adapted to PL/SQL or other tools.)
                              */
                              
                              
                              PROMPT     ==========  0. Basic Pivot  ==========
                              
                              SELECT     deptno
                              ,     COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)     AS analyst_cnt
                              ,     COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)     AS clerk_cnt
                              ,     COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)     AS manager_cnt
                              FROM     scott.emp
                              WHERE     job     IN ('ANALYST', 'CLERK', 'MANAGER')
                              GROUP BY     deptno
                              ORDER BY     deptno
                              ;
                              
                              
                              PROMPT     ==========  1. Dynamic Pivot  ==========
                              
                              --     *****  Start of dynamic_pivot.sql  *****
                              
                              -- Suppress SQL*Plus features that interfere with raw output
                              SET     FEEDBACK     OFF
                              SET     PAGESIZE     0
                              
                              SPOOL     p:\sql\cookbook\dynamic_pivot_subscript.sql
                              
                              SELECT     DISTINCT
                                   ',     COUNT (CASE WHEN job = '''
                              ||     job
                              ||     ''' '     AS txt1
                              ,     'THEN 1 END)     AS '
                              ||     job
                              ||     '_CNT'     AS txt2
                              FROM     scott.emp
                              ORDER BY     txt1;
                              
                              SPOOL     OFF
                              
                              -- Restore SQL*Plus features suppressed earlier
                              SET     FEEDBACK     ON
                              SET     PAGESIZE     50
                              
                              SPOOL     p:\sql\cookbook\dynamic_pivot.lst
                              
                              SELECT     deptno
                              @@dynamic_pivot_subscript
                              FROM     scott.emp
                              GROUP BY     deptno
                              ORDER BY     deptno
                              ;
                              
                              SPOOL     OFF
                              
                              --     *****  End of dynamic_pivot.sql  *****
                              
                              /*
                              EXPLANATION:
                              The basic pivot assumes you know the number of distinct jobs,
                              and the name of each one.  If you do, then writing a pivot query
                              is simply a matter of writing the correct number of ", COUNT ... AS ..."\
                              lines, with the name entered in two places on each one.  That is easily
                              done by a preliminary query, which uses SPOOL to write a sub-script 
                              (called dynamic_pivot_subscript.sql in this example).
                              
                              The main script invokes this sub-script at the proper point.
                              In practice, .SQL scripts usually contain one or more complete
                              statements, but there's nothing that says they have to.
                              This one contains just a fragment from the middle of a SELECT statement.
                              
                              Before creating the sub-script, turn off SQL*Plus features that are 
                              designed to help humans read the output (such as headings and 
                              feedback messages like "7 rows selected.", since we do not want these 
                              to appear in the sub-script.
                              Turn these features on again before running the main query.
                              
                              */
                              If you need help adapting this to your problem, post some sample data and desired results for a couple of scenarios.
                              For example:
                              "If I have this data: CREATE TABLE table_x AS ...
                              then I need these results.
                              But if I add these 2 rows: INSERT INTO table_x ...
                              then I need these results ..."
                              • 12. Re: GROUP BY clause with Alias and Column Header
                                633450
                                <ul><li>Thanks for your help!
                                     I tried to import your query into my existing query but getting error.
                                     I love to post table/Insert script but i have lots of join tables and also using to retrieve data from those tables in my output results.
                                     If you see my original query then still you need crate table / Insert data script then please let me know.

                                     This is my original query:
                                <ul><li>SELECT  s_name ,login, f_name, l_name, email, S_ID, S_EMAIL,</li>
                                <li>desc, wave, </li>
                                <li>MAX(DECODE(C_CODE, '100',S_TASK, '')) "100",</li>
                                <li>MAX(DECODE(C_CODE, '150',S_TASK, '')) "150",</li>
                                <li>MAX(DECODE(C_CODE, '200',S_TASK, '')) "200"</li>
                                <li>FROM </li>
                                <li>+(+</li>
                                <li>SELECT  b.S_name, d.login, d.f_name,d.l_name, d.email,</li>
                                <li>+(SELECT d.login FROM user d WHERE d.id = c.rep_to_id) AS S_ID,+</li>
                                <li>+(SELECT d.email FROM user d WHERE d.id = c.rep_to_id) AS S_EMAIL,+</li>
                                <li>e.desc, f.wave, C_CODE, S_TASK</li>
                                <li> FROM CT_E_STAK_MAP    A,</li>
                                <li> ct_stak               B,</li>
                                <li> e_info                 C,</li>
                                <li> user                               D,</li>
                                <li> e_code               E,</li>
                                <li> e_web_info                     F, </li>
                                <li> C_STAK_MAP                G,</li>
                                <li> C_CORS                        H, </li>
                                <li> C_S_TASK                     I,</li>
                                <li> C_S_STAK_MAP                        J</li>
                                <li>WHERE a.C_stak_id     = b.c_stak_id </li>
                                <li>AND   b.c_stak_id         = G.c_stak_id</li>
                                <li>AND   B.C_STAK_ID     = J.C_STAK_ID</li>
                                <li>AND   J.C_S_TASK_ID             = I.C_S_TASK_ID</li>
                                <li>AND   G.C_CORS_ID               = H.C_CORS_ID</li>
                                <li>AND   a.U_id                = c.U_id </li>
                                <li>AND   a.U_id                = d.id </li>
                                <li>AND   d.id              = f.U_id </li>
                                <li>AND   c.ctll3                 = e.c_st_val </li>
                                <li>AND   c.ctl1_2              = e.ctl1_2 </li>
                                <li>AND   e.c_set_name     = 'EY' </li>
                                <li>AND   c.active              = 1</li>
                                <li>GROUP BY b.s_name, d.login, d.f_name,d.l_name, d.email,d.login, d.email, S_ID, S_EMAIL,</li>
                                <li> e.desc, f.wave, C_CODE, S_TASK</li>
                                <li>+)+</li>
                                <li>GROUP BY s_name ,login, f_name, l_name, email, S_ID, S_EMAIL, desc, wave</li>
                                </ul>
                                </li>
                                <li>I tried include your query in my block</li>
                                <li><ul><li>SELECT  s_name ,login, f_name, l_name, email, S_ID, S_EMAIL,</li>
                                <li>desc, wave, </li>
                                <li>+(SELECT DISTINCT+</li>
                                <li> +', COUNT (CASE WHEN C_CODE = '''+</li>
                                <li>+|| C_CODE+</li>
                                <li>+|| ''' ' AS txt1+</li>
                                <li>+, 'THEN 1 END) AS '+</li>
                                <li>+|| C_CODE+</li>
                                <li>+|| '_CNT' AS txt2+</li>
                                <li>FROM  C_CORS H1</li>
                                <li>ORDER BY       txt1)</li>
                                <li>FROM </li>
                                <li>+(+</li>
                                <li>SELECT  b.S_name, d.login, d.f_name,d.l_name, d.email,</li>
                                <li>+(SELECT d.login FROM user d WHERE d.id = c.rep_to_id) AS S_ID,+</li>
                                <li>+(SELECT d.email FROM user d WHERE d.id = c.rep_to_id) AS S_EMAIL,+</li>
                                <li>e.desc, f.wave, C_CODE, S_TASK</li>
                                <li> FROM CT_E_STAK_MAP    A,</li>
                                <li> ct_stak               B,</li>
                                <li> e_info                 C,</li>
                                <li> user                               D,</li>
                                <li> e_code               E,</li>
                                <li> e_web_info                     F, </li>
                                <li> C_STAK_MAP                G,</li>
                                <li> C_CORS                        H, </li>
                                <li> C_S_TASK                     I,</li>
                                <li> C_S_STAK_MAP                        J</li>
                                <li>WHERE a.C_stak_id     = b.c_stak_id </li>
                                <li>AND   b.c_stak_id         = G.c_stak_id</li>
                                <li>AND   B.C_STAK_ID     = J.C_STAK_ID</li>
                                <li>AND   J.C_S_TASK_ID             = I.C_S_TASK_ID</li>
                                <li>AND   G.C_CORS_ID               = H.C_CORS_ID</li>
                                <li>AND   a.U_id                = c.U_id </li>
                                <li>AND   a.U_id                = d.id </li>
                                <li>AND   d.id              = f.U_id </li>
                                <li>AND   c.ctll3                 = e.c_st_val </li>
                                <li>AND   c.ctl1_2              = e.ctl1_2 </li>
                                <li>AND   e.c_set_name     = 'EY' </li>
                                <li>AND   c.active              = 1</li>
                                <li>GROUP BY b.s_name, d.login, d.f_name,d.l_name, d.email,d.login, d.email, S_ID, S_EMAIL,</li>
                                <li> e.desc, f.wave, C_CODE, S_TASK</li>
                                <li>+)+</li>
                                <li>GROUP BY s_name ,login, f_name, l_name, email, S_ID, S_EMAIL, desc, wave</li>
                                </ul>
                                </li>
                                <li>I want result like:</li>
                                <li>+
                                S_name ,login, f_name, l_name, email, S_ID, S_EMAIL, desc, wave, C_Code1, C_code2, C_Code (This will dynamically)


                                +p p1 p1s s [p@email.com|mailto:p@email.com] d1 [d1@email.com|mailto:d1@email.com] 'test' wave1 Eco Math Eng....+

                                +</li>
                                </ul>
                                +
                                I hope explained properly what you are asking.+

                                +Thanks once again!

                                +
                                • 13. Re: GROUP BY clause with Alias and Column Header
                                  Frank Kulash
                                  Hi,
                                  user630447 wrote:
                                  ...
                                  <li>I want result like:</li>
                                  <li>+
                                  S_name ,login, f_name, l_name, email, S_ID, S_EMAIL, desc, wave, C_Code1, C_code2, C_Code (This will dynamically)


                                  +p p1 p1s s [p@email.com|mailto:p@email.com] d1 [d1@email.com|mailto:d1@email.com] 'test' wave1 Eco Math Eng....+

                                  +</li>
                                  </ul>
                                  +
                                  I hope explained properly what you are asking.+
                                  Unfortunately, that's not at all like what I requested, which was
                                  "If I have this data: CREATE TABLE table_x AS ...
                                  then I need these results.
                                  But if I add these 2 rows: INSERT INTO table_x ...
                                  then I need these results ..."

                                  It looks like (the code is so badly formatted I can't be sure) you're doing two things:
                                  (1) a query involving lots of tables and lots of join conditions, and then
                                  (2) pivoting the results of (1).

                                  Are you getting the correct data from step (1)? Is the problem just that you don't know how to re-arrange the results of (1) into the format you want?
                                  If so, post the results of (1). You can probably show the problem with about 10 rows of output from (1). Also post the results you want from that data after step (2), perhaps 3 or 4 rows.
                                  • 14. Re: GROUP BY clause with Alias and Column Header
                                    SomeoneElse
                                    Are
                                     tags really that hard to use?
                                    
                                    Everything you need to know is in here: http://forums.oracle.com/forums/help.jspa                                                                                                                                                                                                                                                            
                                    1 2 Previous Next