none
How to make group by to these select to show on one line RRS feed

  • Question

  • Problem

    How to show this statement on one line by group by ?

    select  iif((CreditSum > DebitSum), abs(CreditSum - DebitSum), 0) as Credit, iif((CreditSum < DebitSum), abs(CreditSum - DebitSum), 0) as Debit , iif((EqCreditSum > EqDebitSum), abs(EqCreditSum - EqDebitSum), 0) as EqCredit, iif((EqCreditSum < EqDebitSum), abs(EqCreditSum - EqDebitSum), 0) as EqDebit ,  BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
    CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, TotalDeposite, PaymentDate, CheckValue,AccCode,AccAraName,AccEngName, DocNo,FDesc,Recip,
    						 SubLdgCodeType1, SubLdgCode1, SubLdgCodeType2, 
                             SubLdgCode2, SubLdgCodeType3,  SubLdgCode3,  SubLdgCodeType4, SubLdgCode4,  SubLdgCodeType5, SubLdgCode5, 
                              TrxDate, ReceiveDate, ExpenseArea, UnitValue, GardenArea,Notes,ProjectClassId
     from (  
    SELECT        BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
                             CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, 
                             TotalDeposite, PaymentDate, CheckValue, null AS TrxDate, '' AS AccCode, '' AS AccAraName, '' AS AccEngName, SUM(Credit) AS CreditSum, SUM(Debit) AS DebitSum, 
                             SUM(EqDebit) AS EqDebitSum, SUM(EqCredit) AS EqCreditSum, '' AS DocNo, 'رصيد ماقبله' AS FDesc, '' AS SubLdgCodeType1, '' AS SubLdgCode1, '' AS SubLdgCodeType2, 
                             '' AS SubLdgCode2, '' AS SubLdgCodeType3, '' AS SubLdgCode3, '' AS SubLdgCodeType4, '' AS SubLdgCode4, '' AS SubLdgCodeType5, '' AS SubLdgCode5, 'رصيد ماقبله'  AS Recip, 
                             ReceiveDate, ExpenseArea, UnitValue, GardenArea, 'رصيد ما قبله' as Notes,ProjectClassId
    FROM            View_UOUnitsTrxSafe  Where  ((OpeningRec = 0) OR (OpeningRec IS NULL)) AND ((closingRec = 0) OR (closingRec IS NULL))   and  TrxDate < '2018/01/01' and  ProjectCode >= 31 and  ProjectCode <= 32 and  UnitCode >= 101 and UnitCode <= 103 GROUP BY BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
                             CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, 
                             TotalDeposite, PaymentDate, CheckValue, ReceiveDate, ExpenseArea, UnitValue, GardenArea,ProjectClassId
    					
    						 Union All
    
                SELECT       BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
                        CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, 
                        TotalDeposite, PaymentDate, CheckValue, TrxDate, AccCode, AccAraName, AccEngName, Credit, Debit, EqDebit, EqCredit, DocNo, FDesc, SubLdgCodeType1, 
                        SubLdgCode1, SubLdgCodeType2, SubLdgCode2, SubLdgCodeType3, SubLdgCode3, SubLdgCodeType4, SubLdgCode4, SubLdgCodeType5, SubLdgCode5, Recip, 
                         ReceiveDate, ExpenseArea, UnitValue, GardenArea,Notes,ProjectClassId
                FROM            dbo.View_UOUnitsTrxSafe  Where  ((OpeningRec = 0) OR (OpeningRec IS NULL)) AND ((closingRec = 0) OR (closingRec IS NULL))   and  TrxDate >= '2018/01/01' and  TrxDate <= '2018/12/13' and  ProjectCode >= 31 and  ProjectCode <= 32 and  UnitCode >= 101 and UnitCode <= 103) as UOUnitsTrxSafe

    Result i need is to display one line 

    my view above  result 

    Result i need to show is to display two lines as one line 

    Debit      eqdebit credit  eqcredit customercode project unit
    15958.2    15958.2   0        0      24040916     31     112 
    So that how to add group by to query above to show as one line

    Friday, December 14, 2018 2:39 PM

Answers

  • Select sum(Debit) debit, Sum(eqdebit) eqdebit , Sum(credit) credit,
      customercode, project, unit
    from (
    
    select  iif((CreditSum > DebitSum), abs(CreditSum - DebitSum), 0) as Credit, iif((CreditSum < DebitSum), abs(CreditSum - DebitSum), 0) as Debit , iif((EqCreditSum > EqDebitSum), abs(EqCreditSum - EqDebitSum), 0) as EqCredit, iif((EqCreditSum < EqDebitSum), abs(EqCreditSum - EqDebitSum), 0) as EqDebit ,  BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
    CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, TotalDeposite, PaymentDate, CheckValue,AccCode,AccAraName,AccEngName, DocNo,FDesc,Recip,
    						 SubLdgCodeType1, SubLdgCode1, SubLdgCodeType2, 
                             SubLdgCode2, SubLdgCodeType3,  SubLdgCode3,  SubLdgCodeType4, SubLdgCode4,  SubLdgCodeType5, SubLdgCode5, 
                              TrxDate, ReceiveDate, ExpenseArea, UnitValue, GardenArea,Notes,ProjectClassId
     from (  
    SELECT        BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
                             CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, 
                             TotalDeposite, PaymentDate, CheckValue, null AS TrxDate, '' AS AccCode, '' AS AccAraName, '' AS AccEngName, SUM(Credit) AS CreditSum, SUM(Debit) AS DebitSum, 
                             SUM(EqDebit) AS EqDebitSum, SUM(EqCredit) AS EqCreditSum, '' AS DocNo, 'رصيد ماقبله' AS FDesc, '' AS SubLdgCodeType1, '' AS SubLdgCode1, '' AS SubLdgCodeType2, 
                             '' AS SubLdgCode2, '' AS SubLdgCodeType3, '' AS SubLdgCode3, '' AS SubLdgCodeType4, '' AS SubLdgCode4, '' AS SubLdgCodeType5, '' AS SubLdgCode5, 'رصيد ماقبله'  AS Recip, 
                             ReceiveDate, ExpenseArea, UnitValue, GardenArea, 'رصيد ما قبله' as Notes,ProjectClassId
    FROM            View_UOUnitsTrxSafe  Where  ((OpeningRec = 0) OR (OpeningRec IS NULL)) AND ((closingRec = 0) OR (closingRec IS NULL))   and  TrxDate < '2018/01/01' and  ProjectCode >= 31 and  ProjectCode <= 32 and  UnitCode >= 101 and UnitCode <= 103 GROUP BY BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
                             CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, 
                             TotalDeposite, PaymentDate, CheckValue, ReceiveDate, ExpenseArea, UnitValue, GardenArea,ProjectClassId
    					
    						 Union All
    
                SELECT       BranchCode, UnitCode, UnitAraName, UnitLatName, ProjectCode, ProjectAraName, ProjectLatName, CustomerCode, CustomerAraName, CustomerLatName, 
                        CustomerTele, CustomerMob1, CustomerMob2, CustomerAddress, LandArea, DepositePrecentage, DepositeValue, DepositeIncreaseValue, StartDealDate, 
                        TotalDeposite, PaymentDate, CheckValue, TrxDate, AccCode, AccAraName, AccEngName, Credit, Debit, EqDebit, EqCredit, DocNo, FDesc, SubLdgCodeType1, 
                        SubLdgCode1, SubLdgCodeType2, SubLdgCode2, SubLdgCodeType3, SubLdgCode3, SubLdgCodeType4, SubLdgCode4, SubLdgCodeType5, SubLdgCode5, Recip, 
                         ReceiveDate, ExpenseArea, UnitValue, GardenArea,Notes,ProjectClassId
                FROM            dbo.View_UOUnitsTrxSafe  Where  ((OpeningRec = 0) OR (OpeningRec IS NULL)) AND ((closingRec = 0) OR (closingRec IS NULL))   and  TrxDate >= '2018/01/01' and  TrxDate <= '2018/12/13' and  ProjectCode >= 31 and  ProjectCode <= 32 and  UnitCode >= 101 and UnitCode <= 103) as UOUnitsTrxSafe) t
    
    Group by  customercode, project, unit


    Friday, December 14, 2018 2:44 PM
    Moderator