Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: Regarding Aggregate conditions ..

    Hai frns small help needed.


    I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

    One special condition is as follows:


    For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
    If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
    Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

    Here is the query is used

    select * from sample

    id ssn credit flag sem
    1 101 0 C9 0
    2 101 4 C9 3
    3 101 4.5 C9 2
    4 101 3.5 C1 1
    5 102 4.2 C3 3
    6 103 0 C1 2


    select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
    group by ssn,flag


    ssn flag sum_val
    101 C1 3.5
    103 C1 2.0
    102 C3 4.2
    101 C9 8.5

    The above output is wrong one.


    Expected output

    101 4.5+3.5=8.0
    102 4.2
    103 2.0


    Any help would be appreciated

    Regards,

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT    ssn
            , SUM(max_credit)
    FROM    --MAX credit\ sem per ssn & flag
            (SELECT    dbo.my_table.ssn
                    , max_credit        = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
            FROM    dbo.my_table
            GROUP BY dbo.my_table.ssn
                    , dbo.my_table.flag) AS mc
    GROUP BY ssn

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually I think that is flawed. I don't think your sample data is comprehensive enough.

  4. #4
    Join Date
    Nov 2007
    Posts
    41
    Thanks, Here is more sample data

    101 0 C9 0
    101 4 C9 3
    101 4.5 C9 2
    101 0 C9 2
    101 3.5 C1 1
    101 3.5 C2 2
    104 3.5 C1 3
    105 3.5 C2 0
    106 3.5 C3 1
    107 3.5 C4 1
    109 3.5 C6 4
    110 3.5 C7 1

    Regards,

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ssn
         , SUM(case when max_credit = 0 
                    then sem_for_max_credit
                    else max_credit  end) as daSum
      FROM (
           SELECT ssn
                , flag
                , sem    as sem_for_max_credit 
                , credit as max_credit
             FROM daTable as T
            WHERE credit =
                  ( SELECT MAX(credit)
                      FROM daTable
                     WHERE ssn = T.ssn
                       AND flag = T.flag ) 
           ) AS maxes
    GROUP 
        BY ssn
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2007
    Posts
    41
    Thanks for the help.

Posting Permissions

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