Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Location
    Pune, India
    Posts
    7

    Unanswered: Simple Group By SQL Query

    Dear All

    I am new for this Forum and SQL.

    I have table:

    Code:
    ID       Name         Type           Sal
    --------------------------------------------
    1        A            X             2000
    1        B            Y             6000
    2        C            Y             3000
    2        D            X             3000
    3        A            X             2000
    3        D            Y             1000

    I want result like this:

    Code:
    ID       Name         Type           Sal
    --------------------------------------------
    1        A            X              8000
    2        D            X              6000
    3        A            X              3000
    Get sum of "Sal" as per the group of "ID" and get other details (Name, Type, ID) from the record where Type = X.


    How can I create SQL query to get this result.

    Appreciate all your help.


    Regards
    Somnath

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    exact query depends on answers on these two questions:

    What shall be the result when there is no row with TYPE='X' for given ID?
    What shall be the result when there are multiple rows with TYPE='X' for given ID?

    Anyway, if there are no duplicates with TYPE='X' or you do not mind mixing values from those rows, you may use CASE/DECODE expression in any aggregate function accepting strings, e.g.
    Code:
    select id,
           max( case when type = 'X' then name end ) name,
           <similar expression for type or any other column>,
           sum(sal) sal
    from <table name you did not reveal>
    group by id;

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Another option (worse than Flyboy's, though):
    Code:
    select 
      t.id,
      (select t1.name from test t1
       where t1.id = t.id
         and t1.type = 'X'
      ) name,
      (select t1.type from test t1
       where t1.id = t.id
         and t1.type = 'X'
      ) type,
      sum(t.sal)
    from test t
    group by t.id

  4. #4
    Join Date
    Dec 2011
    Location
    Pune, India
    Posts
    7
    Thank you so much...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •