4 Replies Latest reply on Apr 23, 2013 7:53 AM by 947771

    how to use other cols in group by which does not need grouping.

    947771
      HI,
      saleshistory table has sales record of each empoly, day wise.
      that is saleshistory has more than one record of empid in it.

      empcode is number(9,0) and empname is varchar2(200), sales number(10,5),empid is number(10,0)


      select empid, sum(sales), max(empname), max(empcode) from saleshistory

      group by empid ;

      or

      select empid, sum(sales), empname,empcode from saleshistory

      group by empid ,empname,empcode ;



      i want to find the total amout of sales done by each employee with empid,empname,empcode in select list.

      1) please tel me which method good which i should follow or is there any other good way to get it.
      yours sincerely

      Edited by: 944768 on Apr 20, 2013 5:14 AM

      Edited by: 944768 on Apr 20, 2013 5:15 AM

      Edited by: 944768 on Apr 20, 2013 5:15 AM

      Edited by: 944768 on Apr 20, 2013 5:16 AM

      Edited by: 944768 on Apr 20, 2013 5:34 AM

      Edited by: 944768 on Apr 20, 2013 7:08 AM
        • 1. Analytic SUM
          Frank Kulash
          Hi,

          This sounds like a job for the analytic SUM function.

          Since you didn't post CREATE TABLE and INSERT statements for your own table, I can't test with your table. I'll show how to do his using the scott.emp table instead. In scott.emp, there can any number of rows with the same job, just as in your table there can be any number of rows for the same empid.
          SELECT       job
          ,       sal
          ,       SUM (sal) OVER  (PARTITION BY  job)     AS total_sal
          FROM       scott.emp
          ORDER BY  job
          ;
          Output:
          JOB              SAL  TOTAL_SAL
          --------- ---------- ----------
          ANALYST         3000       6000
          ANALYST         3000       6000
          CLERK           1300       4150
          CLERK            950       4150
          CLERK            800       4150
          CLERK           1100       4150
          MANAGER         2850       8275
          MANAGER         2975       8275
          MANAGER         2450       8275
          PRESIDENT       5000       5000
          SALESMAN        1500       5600
          SALESMAN        1250       5600
          SALESMAN        1250       5600
          SALESMAN        1600       5600
          Most aggregate functions (like SUM) have analytic counterparts which can get the same results without collapsing the result set down to one row per group. The PARTITION BY clause of analytic functions corresponds to the GROUP BY clause used with aggregate functions.
          944768 wrote:
          ... when we used GTT in Stored proc (SP) is it necessary to truncate it at the begining of sp , if one truncate can it create any harm, as SP is used by many people at the same time.
          This seems to be a completely separate question. Most of your message involved grouping and the SUM function; it has nothing to do with Global Temporary Tables or stored procedures. You might have an application that uses a stored procedure and a global temporary table, and which also uses groupng and the SUM function, but that doesn't mean you hve a problem that involves all of them. If the stored procedure problem has anything to do with the grouping problem, explain it. If it is a completely separate problem, then start aompletely separate thread or it (and explain it).

           

          I hope this answers your question.
          If not, point out where the query above is producing the wrong reslts, and explain, using specific examples, how you get the correct results in those places.
          If you want to use your own table, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: how to use other cols in group by which does not need grouping.
            John Spencer
            Assumming that each empid will always reference the same empname and empcode, then the two queries are identical, and I would prefer the second version.

            If there can be more than one empname and/or empcode for an empid, then the two are very different. The "correct" one would depend on what you want to show. The first version would give one row for each empid showing total sales andthe max values of empname and empcode. The second one would show the total sales for each unique combination of empid, empname and empcode, potentiaaly multiple rows for an empid.

            John
            • 3. Re: how to use other cols in group by which does not need grouping.
              Peter Gjelstrup
              Hi,
              select   empid,
                       sum (sales),
                       max (empname),
                       max (empcode)
              from     saleshistory
              group by empid;
              
              select   empid,
                       sum (sales),
                       empname,
                       empcode
              from     saleshistory
              group by empid,
                       empname,
                       empcode;
              Which one to use depends on what you are looking for.

              If resultset should be guaranteed to have only one row per empid, then use the first.

              Problem is, that your table is not normalized since the same empid could be stored with different empname and empcode.

              If this redundancy has lead to inconsistency, then your two queries will not give the same resultset. The first could even produce combinations of empid,empname,empcode that never existed

              If you somehow control the redundancy, meaning their will be no inconsistency, I would say the two queries are equally good.

              Regards
              Peter
              • 4. Re: how to use other cols in group by which does not need grouping.
                947771
                This is not orignal table , orignal tables are normalized , it is temporary table made to show reports( a small part of data wherehouseing)
                one empid will always have unique details , except the sales.

                yours sincerely.