You must Sign In to post a response.
  • Category: SQL Server

    Return single row using case in sqlServer

    hi i keep my purchase and issue in my transaction table with type 0 and 1. now i want to retrieve sum of purchase and issue in single row with two column. my query is.........

    select (case when TransType = 0 then SUM(Amount) end) PurchaseAmt, (case when TransType = 1 then SUM(Amount) end) IssueAmt from TransMaster where TransType in (0,1) group by TransType
    abov query return following thow row.

    PurchaseAmt -------------- IssueAmt
    13600123.00 -------------- NULL
    NULL -------------- 12952262.00]
    but my expectation is single row as following.

    PurchaseAmt ------- IssueAmt
    13600123.00 ------- 12952262.00
    is it possible ? thanks to all.
  • #763535
    Following are some tips to do this

    Select * from
    (select SUM(Amount) PurchaseAmt where TransType = 0) as A
    left outer join
    (select SUM(Amount) PurchaseAmt where TransType = 1) as B on 1=1

    By Nathan
    Direction is important than speed

  • #763588

    Hi Rashed Bin Hares,

    In this scenario you can use for CTE (Common Type Expression). it is mostly help to reach your expectation output...

    As bellow i attached the query Example

    with CTE as --Common type expresstion
    (select (case when TransType = 0 then (Amount) end) PurchaseAmt, (case when TransType = 1 then (Amount) end) IssueAmt from TransMaster where TransType in (0,1))
    select sum(PurchaseAmt)PurchaseAmt,sum(IssueAmt) IssueAmt from cte

    Best of luck ...


Sign In to post your comments