CODEWITHSUNDEEP
sql-servert-sqljoingroup-byleft-join
Jaime Sangcap
Jaime Sangcap

Reputation: 2595

Left Join and Group By with possible empty row

I have these tables

`Sales.Branch`
-Id
-Name
        ==Rows in Sales.Branches==
         - Al Ain
         - Chamber
         - Hamdan
         - Mina
         - Marina
         - EMTI

`Sales.Transaction`

- Id
- Date
- BranchId

`Sales.TransactionItem`
- Id
- TransactionId
- Pages
- Rate

I want to get a report of their total Sales, TotalPages and No. OF Transaction on specific Start and End Dates.

I've tried this query but it only returns Branches that have transactions on specified dates.

select
    b.Name as BranchName,
    COUNT(t.Id) as Transactions,
    SUM(ti.Pages * ti.Rate) as TotalSales,
    SUM(ti.Pages) as Pages

    from 
        Sales.[Transaction] t
            left join Sales.TransactionItem ti
            on ti.TransactionId = t.Id
            left join Sales.Branch b on b.Id = t.BranchId
    where t.Date >= '2016-01-01'
AND t.Date <= '2016-02-01'
    group by b.Name
    order by b.Name ASC

enter image description here

Since the EMTI Branch doesn't have Transaction on 2016-01-01 until 2016-02-01 it is not included in the result.

What I want is include ALL branches even they dont have Transaction, and display 0 on TotalSales, TotalPAges, Transactions instead.

Upvotes: 0

Views: 316

Answers (2)

Zbynek Vyskovsky - kvr000
Zbynek Vyskovsky - kvr000

Reputation: 18825

Use Branch as the main table and put condition on time to join:

select
    b.Name as BranchName,
    COUNT(t.Id) as Transactions,
    SUM(ISNULL(ti.Pages, 0) * ISNULL(ti.Rate, 0)) as TotalSales,
    SUM(ISNULL(ti.Pages, 0)) as Pages
from 
    Sales.Branch b
        left join Sales.[Transaction] t 
            on b.Id = t.BranchId 
            and t.Date >= '2016-01-01'
            AND t.Date <= '2016-02-01'
        left join Sales.TransactionItem ti
            on ti.TransactionId = t.Id
group by b.Name
order by b.Name ASC

Upvotes: 2

Muhammad Azim
Muhammad Azim

Reputation: 329

Please select data from branch table as a parent table and check isnull

select
b.Name as BranchName,
COUNT(ISNULL(t.Id,0)) as Transactions,
SUM(ISNULL(ti.Pages,0) * ISNULL(ti.Rate,0)) as TotalSales,
SUM(ISNULL(ti.Pages,0)) as Pages

from Sales.Branch b 
    left join  Sales.[Transaction] t on b.Id = t.BranchId
        left join Sales.TransactionItem ti
        on ti.TransactionId = t.Id
where t.Date >= '2016-01-01'
AND t.Date <= '2016-02-01'
group by b.Name
order by b.Name ASC

Upvotes: 0

Related Questions