1 2 Previous Next 16 Replies Latest reply: Oct 11, 2012 5:58 AM by chris227 RSS

    group by returning more number of rows

    makdutakdu
      hi all


      this is the original query
      SELECT DISTINCT opn_job_desc, seq, tran_type, customer_name, customer_number
                                    
      FROM                  napesco_log_generic_sum gen,
                        
                        per_people_x eng,                 
                            (SELECT * FROM per_people_x) cell 
                            WHERE gen.engineer1 = eng.employee_number(+)  
                            AND gen.cell_leader = cell.employee_number(+)  
                          and gen.opn_job_desc='J/D/UD102/SF31/958/D/1211/1'
                            GROUP BY  opn_job_desc, 
                            seq,tran_type,customer_name, customer_number
      output is

      OPN_JOB_DESC     SEQ     TRAN_TYPE     CUSTOMER_NAME     CUSTOMER_NUMBER
      J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002
      J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002


      modified query
      SELECT DISTINCT opn_job_desc, seq, tran_type, customer_name, customer_number,RAc.TRX_DATE
                                    
      FROM                  napesco_log_generic_sum gen,
                        ra_customer_trx_all rac,
                        per_people_x eng,                 
                            (SELECT * FROM per_people_x) cell 
                            WHERE gen.engineer1 = eng.employee_number(+)  
                            AND gen.cell_leader = cell.employee_number(+)  
                                       and gen.opn_job_desc=rac.attribute1
                          and gen.opn_job_desc='J/D/UD102/SF31/958/D/1211/1'
                            GROUP BY  opn_job_desc, 
                            seq,tran_type,customer_name, customer_number,RAc.TRX_DATE
      output is

      OPN_JOB_DESC     SEQ     TRAN_TYPE     CUSTOMER_NAME     CUSTOMER_NUMBER     TRX_DATE
      J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002     12/09/2011
      J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002     12/19/2011
      J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002     12/09/2011
      J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002     12/19/2011


      when rac.trx_date is added to group by clause it returns 4 rows
      is it possible to maintain the same number of rows

      kindly guide
        • 1. Re: group by returning more number of rows
          Arild
          It's certainly possible, but then you'll need to decide what to do with the date column. The trx_date is different for
          each of the two original rows ( 12/09/2011 and 12/19/2011 ), so they are split when you group by that column as well.
          An aggregate function such as MAX(), perhaps, without grouping by trx_date?
          Please post your desired output, including the date, if this didn't help.
          • 2. Re: group by returning more number of rows
            makdutakdu
            hi

            i would like to obtain trx_date separately for the material and services

            TRAN_TYPE TRX_DATE

            mATERIAL 12/9/2011
            SERVICES 12/19/2011


            final desired output is

            OPN_JOB_DESC     SEQ     TRAN_TYPE     CUSTOMER_NAME     CUSTOMER_NUMBER trx_date
            J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002 12/9/2011
            J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002 12/19/2011


            Kindly guide me

            Edited by: makdutakdu on Oct 7, 2012 11:21 AM
            • 3. Re: group by returning more number of rows
              Arild
              It seems you have two dates present for each of the 'joint operations' , right?
              Why do you pick the lower date for the first, and the higher date for the second ?
              In order to write a query, there must be rules. What is the business logic here?

              Honestly, I'm thinking you maybe don't understand exactly why you get multiple rows/ dates?
              In that case you should start over from scratch.
              • 4. Re: group by returning more number of rows
                makdutakdu
                Hi


                the new table added to the existing query was

                ra_customer_trx_all rac


                select trx_date,trx_number,attribute1 from ra_customer_trx_all where attribute1='J/D/UD102/SF31/958/D/1211/1'


                trx_date trx_number attribute1

                12/9/2011 2011-1146-M J/D/UD102/SF31/958/D/1211/1
                12/19/2011 2011-1146-S J/D/UD102/SF31/958/D/1211/1

                where M stands for material
                S stands for services
                why is it that when i join this table rac to the original query i get 4 rows ,i noticed this happens when rac.trx_date is inlcuded in the select clause and group by clause

                kindly guide

                Edited by: makdutakdu on Oct 7, 2012 12:31 PM
                • 5. Re: group by returning more number of rows
                  Arild
                  Because you have several rows (dates) per 'attribute1' (a one to many match) in the rac table?

                  Try
                  SELECT rac.trx_date, count(*)
                    FROM ra_customer_trx_all rac
                   WHERE rac.attribute1 = 'J/D/UD102/SF31/958/D/1211/1';
                  GROUP BY rac.trx_date
                  • 6. Re: group by returning more number of rows
                    makdutakdu
                    hi

                    i am getting

                    TRX_DATE COUNT(*)

                    12/9/2011 1
                    12/19/2011 1
                    • 7. Re: group by returning more number of rows
                      Veeresh.S
                      I have altered SQL of Arild, What is the o/p of this, see if this is what you are looking :

                      select trx_date,trx_number,attribute1 from ra_customer_trx_all where trx_date in (SELECT trx_date FROM ra_customer_trx_all WHERE attribute1 = 'J/D/UD102/SF31/958/D/1211/1'
                      GROUP BY rac.trx_date)
                      • 8. Re: group by returning more number of rows
                        makdutakdu
                        hi


                        my query is why is tht when i add this particular table to the original query(my first post) i get 4 rows ,this happens when trx_date is put in group by clause)

                        kindly guide

                        thanking in advance
                        • 9. Re: group by returning more number of rows
                          Veeresh.S
                          Alrid has explained you properly why you are getting 4 rows when you added ra_customer_trx_all.TRX_DATE column with group clause.

                          Group combines all the rows of same values. so your query is giving correct o/p.

                          every row obtained from 1st query as 2 different TRX_DATE values. So with 2 rows from 1st query it is obvious 2nd query generates 4 rows.

                          in your 2nd query try altering : GROUP BY RAC.TRX_DATE,opn_job_desc,seq,tran_type,customer_name, customer_number and see what you get.

                          even try this

                          select opn_job_desc, seq, tran_type, customer_name, customer_number,RAc.TRX_DATE
                          from
                                    napesco_log_generic_sum gen,
                          ra_customer_trx_all rac,
                          where
                               open_job_desc in ( select opn_job_desc from
                          napesco_log_generic_sum gen,
                          per_people_x eng,
                          (SELECT * FROM per_people_x) cell
                                              where
                          gen.engineer1 = eng.employee_number(+)
                                              AND gen.cell_leader = cell.employee_number(+)
                          and gen.opn_job_desc=rac.attribute1
                                              and gen.opn_job_desc='J/D/UD102/SF31/958/D/1211/1'
                                              GROUP BY opn_job_desc,
                                              seq,tran_type,customer_name, customer_number)

                          You have to change your logic and way you expects from pl/sql.

                          Getting only 2 rows o/p from your 2nd query is impossible. re-work on your exact requirements and queries.

                          Edited by: Veeresh.S on Oct 7, 2012 4:40 PM
                          • 10. Re: group by returning more number of rows
                            makdutakdu
                            hi

                            i tried the query you gave

                            it returns so many trx_ dates which are not related to that particular opn_job_desc

                            is there any way to get just 2 rows inspite of including ra_customer_trx_all in the original query
                            • 11. Re: group by returning more number of rows
                              Frank Kulash
                              Hi,
                              makdutakdu wrote:
                              ... is there any way to get just 2 rows inspite of including ra_customer_trx_all in the original query
                              Sure; in fact, there are many different ways, all producing differnt results. Since I don't knpow what results you want, I can't say which way you should use. You need to post CREATE TABLE and INSERT statments for some sample data, and the results you want from that sample data.
                              See the forum FAQ {message:id=9360002}
                              • 12. Re: group by returning more number of rows
                                makdutakdu
                                hi

                                i am posting the CREATE table and INSERT statements
                                create table napesco_log_generic_sum gen   ( opn_job_desc varchar2(240),seq varchar2(240), tran_type varchar2(240),
                                  customer_name varchar2(240), customer_number varchar2(240),engineer1 varchar2(10),cell_leader varchar2(10))
                                  
                                  
                                  insert into napesco_log_generic_sum gen values('J/D/UD102/SF31/958/D/1211/1','1112082',     'Material'     ,'Joint Operations (KGOC / SAC)',     '1002',
                                  '585','326')
                                insert into napesco_log_generic_sum gen values ('J/D/UD102/SF31/958/D/1211/1','1112083',     'Services','Joint Operations (KGOC / SAC)','1002',
                                '585','326')
                                
                                
                                create table per_people_x( employee_number varchar2(30))
                                
                                
                                select employee_number from per_people_x eng 
                                
                                insert into per_people_x values('585')
                                insert into per_people_x values('326')
                                
                                
                                
                                create table ra_customer_trx_all ( attribute1 varchar2(140),attribute7 varchar2(150), trx_date date)
                                
                                insert into ra_customer_trx_values('J/D/UD102/SF31/958/D/1211/1','2011-1466-S','12/19/2011')
                                insert into ra_customer_trx_values('J/D/UD102/SF31/958/D/1211/1','2011-1466-M','12/9/2011')
                                
                                select  attribute1 ,attribute7 ,trx_date from ra_customer_trx_all where attribute1='J/D/UD102/SF31/958/D/1211/1'
                                this is the original query
                                SELECT DISTINCT opn_job_desc, seq, tran_type, customer_name, customer_number
                                                              
                                FROM                  napesco_log_generic_sum gen,
                                                  
                                                  per_people_x eng,                 
                                                      (SELECT * FROM per_people_x) cell 
                                                      WHERE gen.engineer1 = eng.employee_number(+)  
                                                      AND gen.cell_leader = cell.employee_number(+)  
                                                    and gen.opn_job_desc='J/D/UD102/SF31/958/D/1211/1'
                                                      GROUP BY  opn_job_desc, 
                                                      seq,tran_type,customer_name, customer_number
                                 
                                 
                                output is

                                OPN_JOB_DESC     SEQ     TRAN_TYPE     CUSTOMER_NAME     CUSTOMER_NUMBER
                                J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002
                                J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002
                                modified query
                                SELECT DISTINCT opn_job_desc, seq, tran_type, customer_name, customer_number,RAc.TRX_DATE
                                                              
                                FROM                  napesco_log_generic_sum gen,
                                                  ra_customer_trx_all rac,
                                                  per_people_x eng,                 
                                                      (SELECT * FROM per_people_x) cell 
                                                      WHERE gen.engineer1 = eng.employee_number(+)  
                                                      AND gen.cell_leader = cell.employee_number(+)  
                                                                 and gen.opn_job_desc=rac.attribute1
                                                    and gen.opn_job_desc='J/D/UD102/SF31/958/D/1211/1'
                                                      GROUP BY  opn_job_desc, 
                                                      seq,tran_type,customer_name, customer_number,RAc.TRX_DATE
                                output is

                                OPN_JOB_DESC     SEQ     TRAN_TYPE     CUSTOMER_NAME     CUSTOMER_NUMBER     TRX_DATE
                                J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002     12/09/2011
                                J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002     12/19/2011
                                J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002     12/09/2011
                                J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002     12/19/2011
                                when rac.trx_date is added to group by clause it returns 4 rows
                                is it possible to maintain the same number of rows




                                i would like to obtain trx_date separately for the material and services

                                TRAN_TYPE TRX_DATE

                                mATERIAL 12/9/2011
                                SERVICES 12/19/2011
                                final desired output is

                                OPN_JOB_DESC     SEQ     TRAN_TYPE     CUSTOMER_NAME     CUSTOMER_NUMBER trx_date
                                J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002 12/9/2011
                                J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002 12/19/2011

                                kindly guide

                                thanking in advance

                                Edited by: makdutakdu on Oct 8, 2012 8:08 AM
                                • 13. Re: group by returning more number of rows
                                  Frank Kulash
                                  Hi,
                                  makdutakdu wrote:
                                  ... i am posting the CREATE table and INSERT statement
                                  Thanks. Now we can begin.
                                  ...
                                  create table per_people_x( employee_number varchar2(30))
                                  Does this table play any role in this problem? If so, what? Make sure your sample data and results show the importance of all relevant tables and columns.
                                  ... i would like to obtain trx_date separately for the material and services

                                  TRAN_TYPE TRX_DATE

                                  mATERIAL 12/9/2011
                                  SERVICES 12/19/2011
                                  final desired output is

                                  OPN_JOB_DESC     SEQ     TRAN_TYPE     CUSTOMER_NAME     CUSTOMER_NUMBER trx_date
                                  J/D/UD102/SF31/958/D/1211/1     1112082     Material     Joint Operations (KGOC / SAC)     1002 12/9/2011
                                  J/D/UD102/SF31/958/D/1211/1     1112083     Services     Joint Operations (KGOC / SAC)     1002 12/19/2011
                                  \
                                   tags aren't only for code.  It's okay to use \
                                  tags for any formatted text, including real or desired query output.
                                  kindly guide
                                  How do you decide that the results above are correct?
                                  Why does the row with trna_type='Material' have trx_date 12/9/2011, and not 12/19/2011?
                                  Does it matter which date gets paired to which row in gen? What results would you want of the gen table didn't have exactly the same number of rows as the rac table for a given opn_job_desc?
                                  • 14. Re: group by returning more number of rows
                                    makdutakdu
                                    hi

                                    the date 12/9/2012 is the invoice date for materials alone
                                    the date 12/19/2012 is the invoice date for services alone

                                    i dont knw how to go abt this

                                    kindly guide
                                    1 2 Previous Next