Reputation: 63

SQL How To Control The Grouping Column

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)))))

Upvotes: 3

Answers (3)

dunean

Reputation: 166

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

Upvotes: 2

rslemos

Reputation: 2731

Drawing on the ideas of my previous (and hackish) answer , I could devise the following (even more hackish, though more terse) solution:

SELECT  C.ContactId,C.FirstName,C.LastName,C.CreatedOn,

        CASE 
            WHEN (
                    COUNT(CASE x.paymenttype WHEN 'Credit Card' THEN 1 ELSE NULL END)
                  * COUNT(CASE x.paymenttype WHEN 'Miles Point' THEN 1 ELSE NULL END)
                 ) > 0 THEN 'all'
            WHEN COUNT(CASE x.paymenttype WHEN 'Credit Card' THEN 1 ELSE NULL END) > 0 THEN 'Credit Card'
            ELSE 'Miles Point'
        END 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 (
        Select 
            P.new_paymentId,
            LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
        From new_payment P 
    ) AS x on F.new_paymentid=x.new_paymentId

WHERE   @paymenttype = x.paymenttype OR @paymenttype = 'all'

        -- alternatively, for the RDBMS that allows it:
        -- @paymenttype IN (x.paymenttype, 'all')
GROUP BY C.ContactId,C.FirstName,C.LastName,C.CreatedOn

Edit: simplified the query somewhat to avoid one extra anonymous view

Upvotes: 1

rslemos

Reputation: 2731

WORD OF CAUTION: This solution is really ugly, but I think it works (perhaps with a very poor performance). I'm still thinking of a better approach.

Let's isolate the parsing along with the joins so the full query will be more understandable; we'll also leave the Contact aside, as any ContactId value could be joined at last to get the full Contact details. Please, note that neither group by nor aggregate function appear:

Select 

S.ContactId,
LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
F.new_totalamounttl as totalamount

From 

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

The above subquery "produces" (though not materializes) a table with one row per order per reservation per payment, and parses the payment name (this would be unecessary if your database was normalized). As each order belongs to a single contact, this is also per contact.

Now we filter by @paymenttype:

SELECT  *
FROM    (
    Select 

    S.ContactId,
    LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
    F.new_totalamounttl as totalamount

    From

    SalesOrder S
    left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
    left join new_payment P on F.new_paymentid=P.new_paymentId 
) AS x

WHERE 

@paymenttype = x.paymenttype OR @paymenttype = 'all'

-- alternatively in some databases (rather ugly, but concise):
-- @paymenttype IN (x.paymenttype, 'all')

This in turn is taken as an anonymous view (I'll call this view "A" in the comments below) in the next query, with two branches: the first will collect the rows whose paymenttypes are all the same (i.e. there's no payment of other type); the other branch will collect the other rows (different payment types per order). This time we're already aggregating the amount paid.

-- 1st branch
SELECT  y.ContactId, y.paymenttype, SUM(y.totalamount) AS totalamount
FROM    (
    -- the view A
    SELECT  *
    FROM    (
        Select 

        S.ContactId,
        LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
        F.new_totalamounttl as totalamount

        From

        SalesOrder S
        left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
        left join new_payment P on F.new_paymentid=P.new_paymentId 
    ) AS x

    WHERE 

    x.paymenttype = @paymenttype OR @paymenttype = 'all'
) AS y

WHERE 

NOT EXISTS (
    SELECT  *
    FROM    (
        -- the view A (again)
        SELECT  *
        FROM    (
            Select 

            S.ContactId,
            LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
            F.new_totalamounttl as totalamount

            From

            SalesOrder S
            left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
            left join new_payment P on F.new_paymentid=P.new_paymentId 
        ) AS x

        WHERE 

        x.paymenttype = @paymenttype OR @paymenttype = 'all'

    ) AS y1

    WHERE   y.ContactId = y1.ContactId
        AND y.paymenttype <> y1.paymenttype
)

GROUP BY y.ContactId, y.paymenttype

UNION ALL

-- 2nd branch
SELECT  y.ContactId, 'all' AS paymenttype, SUM(y.totalamount) AS totalamount
FROM    (
    -- the view A
    SELECT  *
    FROM    (
        Select 

        S.ContactId,
        LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
        F.new_totalamounttl as totalamount

        From

        SalesOrder S
        left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
        left join new_payment P on F.new_paymentid=P.new_paymentId 
    ) AS x

    WHERE 

    x.paymenttype = @paymenttype OR @paymenttype = 'all'
) AS y

WHERE 

EXISTS (
    SELECT  *
    FROM    (
        -- the view A (again)
        SELECT  *
        FROM    (
            Select 

            S.ContactId,
            LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
            F.new_totalamounttl as totalamount

            From

            SalesOrder S
            left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
            left join new_payment P on F.new_paymentid=P.new_paymentId 
        ) AS x

        WHERE 

        x.paymenttype = @paymenttype OR @paymenttype = 'all'

    ) AS y1

    WHERE   y.ContactId = y1.ContactId
        AND y.paymenttype <> y1.paymenttype
)

GROUP BY y.ContactId

Now to the final query, recovering the Contact details. The just above query is taken as an anonymous view to simplify the join:

Select C.ContactId,C.FirstName,C.LastName,C.CreatedOn,
    z.paymenttype, z.totalamount

From Contact C
left join (
    -- 1st branch
    SELECT  y.ContactId, y.paymenttype, SUM(y.totalamount) AS totalamount
    FROM    (
        -- the view A
        SELECT  *
        FROM    (
            Select 

            S.ContactId,
            LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
            F.new_totalamounttl as totalamount

            From

            SalesOrder S
            left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
            left join new_payment P on F.new_paymentid=P.new_paymentId 
        ) AS x

        WHERE 

        x.paymenttype = @paymenttype OR @paymenttype = 'all'
    ) AS y

    WHERE 

    NOT EXISTS (
        SELECT  *
        FROM    (
            -- the view A (again)
            SELECT  *
            FROM    (
                Select 

                S.ContactId,
                LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
                F.new_totalamounttl as totalamount

                From

                SalesOrder S
                left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
                left join new_payment P on F.new_paymentid=P.new_paymentId 
            ) AS x

            WHERE 

            x.paymenttype = @paymenttype OR @paymenttype = 'all'

        ) AS y1

        WHERE   y.ContactId = y1.ContactId
            AND y.paymenttype <> y1.paymenttype
    )

    GROUP BY y.ContactId, y.paymenttype

    UNION ALL

    -- 2nd branch
    SELECT  y.ContactId, 'all' AS paymenttype, SUM(y.totalamount) AS totalamount
    FROM    (
        -- the view A
        SELECT  *
        FROM    (
            Select 

            S.ContactId,
            LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
            F.new_totalamounttl as totalamount

            From

            SalesOrder S
            left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
            left join new_payment P on F.new_paymentid=P.new_paymentId 
        ) AS x

        WHERE 

        x.paymenttype = @paymenttype OR @paymenttype = 'all'
    ) AS y

    WHERE 

    EXISTS (
        SELECT  *
        FROM    (
            -- the view A (again)
            SELECT  *
            FROM    (
                Select 

                S.ContactId,
                LTRIM(RTRIM(left(P.new_name ,(CHARINDEX('-',P.new_name)-1)))) as paymenttype, 
                F.new_totalamounttl as totalamount

                From

                SalesOrder S
                left join new_flightreservation F on S.SalesOrderId=F.new_salesorderid 
                left join new_payment P on F.new_paymentid=P.new_paymentId 
            ) AS x

            WHERE 

            x.paymenttype = @paymenttype OR @paymenttype = 'all'

        ) AS y1

        WHERE   y.ContactId = y1.ContactId
            AND y.paymenttype <> y1.paymenttype
    )

    GROUP BY y.ContactId
) AS z on C.ContactId=z.ContactId

Upvotes: 1

SQL How To Control The Grouping Column

Answers (3)

Related Questions