Select sum shown null value in left join SQL statement

Question

I have three tables and expecting the result as below but i do not know how to correct my sql statement.

select history.company,history.ghacct,rpt_revenue.revenue,rpt_revenue.other, isnull(guest.stay,0) as stay, isnull(guest.nights,0) as nights
from history
left join(select company,count(*) as stay,sum(nights) as nights from guest group by company) guest on guest.company=history.company
left join (select ghacct,sum(revenue) as revenue, sum(other) as other
from rpt_revenue group by ghacct) rpt_revenue on rpt_revenue.ghacct=history.ghacct
where history.type='c' group by history.company, history.ghacct,rpt_revenue.revenue, rpt_revenue.other,guest.stay,guest.nights order by history.company asc;

history

ghacct company type
33 JOINT LTD 10010205687 c
3B GLOBAL   10010350619 c
3E FASHION  10010244145 c
3P INT'L        10010112089 c

guest

company     stay        nights
33 JOINT LTD    01/01/2009  1
33 JOINT LTD    01/06/2009  1
3B GLOBAL   10/02/2019  2
3E FASHION  09/25/2008  6
3P INT'L        08/26/2009  3
3P INT'L        04/26/2010  9

rpt_revenue

ghacct      revenue other
10010205687 20  10
10010205687 10  10
10010350619 30  2
10010244145 15  3
10010112089 16  8
10010112089 4   2

Result

company     ghacct      revenue other   stay    nights
33 JOINT LTD    10010205687 NULL    NULL    2   2
3B GLOBAL   10010350619 NULL    NULL    1   2
3E FASHION  10010244145 NULL    NULL    1   6
3P INT'L        10010112089 NULL    NULL    2   12

Expected result

company     ghacct      revenue other   stay    nights
33 JOINT LTD    10010205687 30  20  2   2
3B GLOBAL   10010350619 30  2   1   2
3E FASHION  10010244145 15  3   1   6
3P INT'L        10010112089 20  10  2   12

Answer 1

I think the main problem with your current query lies in the GROUP BY clause, which should really only be aggregating by company and account. In addition, you might want to use ISNULL for the revenue and other amount, as you are already doing so for stay and nights.

SELECT
    h.company,
    h.ghacct,
    ISNULL(rr.revenue, 0) AS revenue,
    ISNULL(rr.other, 0)   AS other,
    ISNULL(g.stay, 0)     AS stay,
    ISNULL(g.nights, 0)   AS nights
FROM history h
LEFT JOIN
(
    SELECT company, COUNT(*) AS stay, SUM(nights) AS nights
    FROM guest
    GROUP BY company
) g
    ON g.company = h.company
LEFT JOIN
(
    SELECT ghacct, SUM(revenue) AS revenue, SUM(other) AS other
    FROM rpt_revenue
    GROUP BY ghacct
) rr
    ON rr.ghacct = h.ghacct
WHERE
    h.type = 'c'
GROUP BY
    h.company,
    h.ghacct
ORDER BY
    h.company;