Null Rows' Existence Due to Join Table By Grouping

Question

I am struggling with Group By and Null, as below.

I have two tables like below

Table : 1 accountmast       
companyID   accname             category    
102         PURCHASE ACCOUNT    Purchase Account    
102         LOCAL PURCHASE      Purchase Account    
102         SALES ACCOUNT       Sales Account   

Table: 2 ledger     
companyID   name                debit   credit
102         PURCHASE ACCOUNT    4742.3  
102         LOCAL PURCHASE      51106   
102         SALES ACCOUNT               8010
102         SALES ACCOUNT               4330000
102         PURCHASE ACCOUNT    5480000 

And I have queried as below:

select  
case    
when a.catagory ='Purchase Account' then    
l.name  
end as PurchaseAccount, 

case    
when a.catagory ='Purchase Account' then    
sum(coalesce(l.debit,0))-sum(coalesce(l.credit,0) ) 
end as PurAmt,  

case    
when a.catagory = 'Sales Account' then  
l.name      
end as SalesAccount,    
case    
when a.catagory = 'Sales Account' then  
sum(coalesce(l.credit,0))-sum(coalesce(l.debit,0) ) 
end as SalesAmt 

from ledger l join accountmast a    
on l.companyID=a.companyID  
and l.name = a.accname  
where l.companyID=102   
and a.catagory IN('Purchase Account','Sales Account')   
group by l.name,a.catagory  

And the result is:

Purchase Account    PurAmt          Sales Account   SalesAmt
LOCAL PURCHASE      51106.00         NULL           NULL
PURCHASE ACCOUNT    5484742.30       NULL           NULL
NULL                NULL            SALES ACCOUNT  4338010.00

And the requirement is:

Purchase Account    PurAmt           Sales Account   SalesAmt
LOCAL PURCHASE      51106.00         SALES ACCOUNT   4338010.00
PURCHASE ACCOUNT    5484742.30      

What is the solution?.

If I use Group By then it allows Null value to associate tables' columns.

If I use MAX, MIN then it shows the single records. What do I have to do?

If anyone has better solution please suggest.

Answer 1

Note: This is not an answer It is a reference for Quassnoi consideration.

Here certain correction I have made for your consideration.

WITH    q AS
(
SELECT  am.catagory, l.name,
CASE am.catagory WHEN 'Purchase Account' THEN Amount ELSE -Amount     END as Amount -- Here I provide the column name and alter the Column
FROM    accountmast am
    CROSS APPLY
    (
    SELECT  name, SUM(COALESCE(debit, 0) - COALESCE(credit, 0)) AS Amount --Reverse the sum function which is oppose of yours. And your's shows the value in minus.
    FROM    ledger l
    WHERE   l.name = am.accname
    AND l.companyId = am.companyId
    GROUP BY
             name
    ) l
    WHERE   am.companyId = 102
)
SELECT  l1.name as PurACC, l1.Amount as PurAmt, l2.name as SalACC, l2.Amount as SalAmt -- alter the columns for Binding Purpose.
FROM
    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY name) AS rn
        FROM    q
        WHERE   catagory = 'Purchase account'
    ) l1
FULL JOIN
    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY name) AS rn
        FROM    q
        WHERE   catagory = 'Sales account'
    ) l2
ON l2.rn = l1.rn

The above changes is Important otherwise your answer remain as incorrect and throws an error check it.

Answer 2

WITH    q AS
        (
        SELECT  am.category, l.name,
                CASE am.category WHEN 'Purchase Account' THEN amt ELSE -amt END
        FROM    accountmast am
        CROSS APPLY
                (
                SELECT  name, SUM(COALESCE(credit, 0) - COALESCE(debit, 0)) AS amt
                FROM    ledger l
                WHERE   l.name = am.accname
                        AND l.companyId = am.companyId
                GROUP BY
                        name
                ) l
        WHERE   am.companyId = 102
        )
SELECT  l1.name, l1.amt, l2.name, l2.amt
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY name) AS rn
        FROM    q
        WHERE   category = 'Purchase account'
        ) l1
FULL JOIN
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY name) AS rn
        FROM    q
        WHERE   category = 'Sales account'
        ) l2
ON      l2.rn = l1.rn