5 Replies Latest reply: Dec 23, 2012 11:14 AM by 860320 RSS

    Group by Error

    976439
      Hi, I was looking for some help in regards to working out how to group by my query.

      I have the following sql code that I have written (thanks to alot of help from this forum)
      select job_id,
             count(*) job_count,
             names,
             count(names) over (partition by names) AS names_Count, 
             sum(decode(count(job_id),1,1,0)) over (partition by names) names_count_1
      from test
      group by job_id, names; 
      that gives me the answers that I am looking for. What I was looking to find out was if i remove the select 'job_id and count(*) job_count, then this removes the first 2 columns which is what I am looking for, but the results are repeated due to how many names I have. I would like to have this grouped by names but if i remove the job_id in the group by than the answers change.

      How can I keep the job_id (as required for correct set of results) but have the names grouped up?

      Current Output
      Names | Names Count | Names_Count_1
      ---------   -------------------   ----------------------
      A          3                    2    
      A          3                    2  
      A          3                    2 
      B          4                    4 
      B          4                    4
      etc..
      I would like:
      Names | Names Count | Names_Count_1
      ---------   -------------------   ----------------------
      A          3                    2    
      B          4                    4  
      C          1                    1 
      D          2                    1 
      E          4                    4
      etc..
      if somebody could please advise.

      Thanks in advance.
        • 1. Re: Group by Error
          860320
          i think you need distinct values after your group by query

          Select distinct Names | Names Count | Names_Count_1
          From(
          select job_id,
          count(*) job_count,
          names,
          count(names) over (partition by names) AS names_Count,
          sum(decode(count(job_id),1,1,0)) over (partition by names) names_count_1
          from test
          group by job_id, names
          )
          • 2. Re: Group by Error
            MLBrown
            I believe this would give you what you want:
            select distinct name, names_count, names_count_1
              from (select job_id,
                           count(*) job_count,
                           names,
                           count(names) over (partition by names) AS names_Count, 
                           sum(decode(count(job_id),1,1,0)) over (partition by names) names_count_1
                      from test
                     group by job_id, names); 
            • 3. Re: Group by Error
              976439
              Thanks Harsh and MLBrown thats exactly how i required it. Appreciate your help.
              • 4. Re: Group by Error
                976439
                Hi, as posted my query worked with the example, but when I have tried this again I have just noticed that the answers I get using sub select query are different to the select query on its own.

                This gives me correct answers
                select job_id,
                               count(*) job_count,
                               names,
                               count(names) over (partition by names) AS names_Count, 
                               sum(decode(count(job_id),1,1,0)) over (partition by names) names_count_1
                          from test
                         group by job_id, names;
                but when adding the the select distinct:
                select distinct name, names_count, names_count_1
                  from (select job_id,
                               count(*) job_count,
                               names,
                               count(names) over (partition by names) AS names_Count, 
                               sum(decode(count(job_id),1,1,0)) over (partition by names) names_count_1
                          from test
                         group by job_id, names);
                the answers I get are different for the same criterias, any ideas why?
                • 5. Re: Group by Error
                  860320
                  what different result you are getting, can you provide data and result. it will help to understand