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

    Question

  • 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

    Sunday, April 21, 2013 6:12 AM

Answers

All replies

  • The second method,  but your SalesHistory table is not normalized. It should not include EmpName and EmpCode columns at all, it should only have EmpId column.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, April 21, 2013 6:14 AM
  • select empid, DER.sales, empname,empcode from saleshistory JOIN

    ( SELECT empid,MAX(sales) sales FROM saleshistory  GROUP BY empid) as der

    ON saleshistory.empid=DER.empid



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, April 21, 2013 8:17 AM
  • 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

    Hi,

    As "empname,empcode" are also distinct for each given empid, you have to include both as group columns as well.


    Many Thanks & Best Regards, Hua Min

    Sunday, April 21, 2013 1:26 PM