SQL How To Control The Grouping Column

Question

I've created a query.In that query,there are some tables which are joined to another table.I m directly getting the values of columns ContactId,FirstName,LastName,CreatedOn. For paymnettype column,I m parsing the value inside of P.new_name(the values are something like 'Credit Card - 2014-06-29').There are 2 type of value for paymenttype,'Credit Card' and 'Miles Point'

My main purpose in that query is getting sum of the profits of flight reservations per contact,that's why I'm grouping the tables by all columns except totalamount column to make aggregate function work.I m grouping the table by paymenttype because I have to,otherwise it's giving error as expected.

The problem is that if a contact did a reservation in two type of payment,the contactid is being displayed in two columns.

I want to make it displayng in one column,getting sum as usual in the query and writing 'all' in paymenttype column. Is that Possible.If it is ,any idea about how i can do that.

Also I should mention about that,I'll use that query in SSRS ,so any idea to solve the problem in SSRS side would help me

   Select C.ContactId,C.FirstName,C.LastName,C.CreatedOn,

     LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1))))) as paymenttype, 

     SUM(F.new_totalamounttl) as totalamount

     From Contact C 

     left join  SalesOrder S on C.ContactId=S.ContactId
     left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
     left join new_payment P on F.new_paymentid=P.new_paymentId 

     where 
     (LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1))))) IN 
     ( CASE @paymenttype WHEN 'all'
      THEN  (LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))))
      ELSE @paymenttype END )

      GROUP BY C.ContactId,C.FirstName,C.LastName,C.CreatedOn,

       (LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))))

Answer 1

I think by splitting it into two parts, you can get a fairly elegant solution. The first part deals with the PaymentType and the second does the grouping/summing based on that.

SELECT
    ContactId
    ,FirstName
    ,LastName
    ,CreatedOn
    ,SUM(subamount) totalamount
    ,paymenttype
FROM
(
    Select 
        C.ContactId
        ,C.FirstName
        ,C.LastName
        ,C.CreatedOn
        ,@paymenttype as paymenttype
        ,CASE
            WHEN @paymenttype = ‘all’ THEN F.new_totalamounttl
            WHEN @paymenttype = LTRIM(RTRIM(LEFT(P.new_name ,(CHARINDEX('-',P.new_name)-1))))) THEN F.new_totalamounttl
        ELSE 0 END as subamount
    From
        Contact C 
        left join  SalesOrder S 
            on C.ContactId=S.ContactId
        left join new_flightreservation F 
            on S.SalesOrderId=F.new_salesorderid 
        left join new_payment P 
            on F.new_paymentid=P.new_paymentId 
) sub
GROUP BY
     ContactId
     ,FirstName
     ,LastName
     ,CreatedOn
     ,paymenttype