This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 11, 2012 3:58 AM by chris227 RSS

group by returning more number of rows

makdutakdu Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points