multiple sums on grouped tables

Table Header
+---------+-------+
| ref     | total | 
+---------+-------+
| 2544    |  2000 |
| 2544    |    10 |
| 2545    |  2566 |
| 2545    |    34 |
+---------+-------+

Table Items
+---------+-------+
| ref     | total | 
+---------+-------+
| 2544    |  1500 |
| 2544    |   500 |
| 2545    |  2000 |
| 2545    |   500 |
| 2545    |   100 |
+---------+-------+

I would like to sum the total column in both tables grouped by ref, so my output would look something like:

Output:
+---------+--------------+-------------+
| ref     | total_header | total_items | 
+---------+--------------+-------------+
| 2544    |        2010  |       2000  |
| 2545    |        2600  |       2600  |
+---------+--------------+-------------+

I can then compare the two total columns and check for differences.

The query:

Select
    Header.ref,
    Sum(Header.total) as total_header
From
    Header
Group By
    Header.ref
Order By
    Header.ref Desc

This gives me the correct info for the Header table. As soon as I add the Items table to the query then the total_header and total_items give the wrong values

Here is the query that I am using....

Select
    Header.ref,
    Sum(Header.total) as total_header,
    Sum(Items.total) as total_items
From
    Header Left Join
    Items On Header.ref = Items.ref
Group By
    Header.ref
Order By
    Header.ref Desc

Can anyone help me create a query that would produce the output above? Also, if possible with another column that compares the 2 total columns giving true or false depending on if they match.

Thank you,

Martin.

You may first get the sum and then join them -

SELECT H.ref, H_TOT, I_TOT
FROM (SELECT ref, SUM(total) H_TOT
      FROM Header
      GROUP BY ref) H
JOIN (SELECT ref, SUM(total) I_TOT
      FROM Items
      GROUP BY ref) I ON H.ref = I.ref
ORDER BY H.ref

With UNION ALL of the 2 tables and then aggregate:

select t.ref,
  sum(t.total_header) total_header,
  sum(t.total_items) total_items,
  sum(t.total_header) = sum(t.total_items) matching
from (  
  select ref, total total_header, 0 total_items from Header
  union all
  select ref, 0 total_header, total total_items from Items
) t  
group by t.ref

See the demo.<br/>
Results:

| ref  | total_header | total_items | matching |
| ---- | ------------ | ----------- | -------- |
| 2544 | 2010         | 2000        | 0        |
| 2545 | 2600         | 2600        | 1        |

Possible solution

SELECT refs.ref, sum_header.header_total, sum_items.items_total
FROM ( SELECT ref FROM header
       UNION
       SELECT ref FROM items ) refs
LEFT JOIN ( SELECT ref, SUM(total) header_total
            FROM header
            GROUP BY ref ) sum_header USING (ref)
LEFT JOIN ( SELECT ref, SUM(total) items_total
            FROM items
            GROUP BY ref ) sum_items USING (ref)

The best situation for this solution is:

1. SELECT COUNT(*)/COUNT(DISTINCT ref) is high, ~ (10 or more), for both tables.
2. ref is indexed in both tables (additionally try SELECT DISTINCT ref FROM in first subquery for both tables).

You can simply use

Select ref, sum(header.total) as total_header, sum(items.total) as total_items
from header,items where header.ref = items.ref
group by ref
order by ref desc