How can I add an integer from another table to the current selected table in SQL Server?

Question

Does anyone know how can I add an integer from another table to the current selected table in SQL Server?

For example: I have 2 tables with the following information in each table

1. tableA:

   id    username   point   status   country
   1     alvin      1       1        U.S.A
   2     alvin      1       1        U.S.A
   3     amy        1       0        Australia
   

2. tableB:

   id    username    point
   1     amy         1
   2     alvin       1
   3     ken         1
   

How can I sum up the total points in tableA with also add in the sum points from tableB? I tried the following code, but seem is not working and error display:

SELECT username, (COUNT(distinct a.point) + (SELECT SUM(a.point)
    FROM tableB b WHERE b.username = a.username) AS 'Points', status, country
FROM tableA
GROUP BY aco.username

And the output I expected will be:

username Points status country
alvin    3      1      U.S.A
amy      2      0      Australia

Answer 1

WITH Results(username,point)
AS
(
SELECT username, point FROM TableA
UNION ALL
SELECT username, point FROM TableB
)

SELECT username, sum(point) AS Points FROM Results GROUP BY username
GO

EDIT

The question has changed, so now the solution should look like this

WITH Results(username,point,status, country)
AS
(
SELECT username, point, status, country FROM TableA
UNION ALL
SELECT username, point, null, null FROM TableB
)

SELECT username, sum(point) AS Points, max(status), max(country) FROM Results GROUP BY username
GO

What is WITH ? What is UNION ?

Answer 2

You don't mention why Ken doesn't appear in the output table, I assume that TableA is the 'master' list. If so I would do the following INNER JOIN which is the most simple solution.

SELECT a.username AS Username, SUM(ISNULL(a.point,0)+ISNULL(b.point,0)) as Points,
   MAX(a.Status) as Status, MAX(a.Country) as Country
FROM TableA a 
INNER JOIN TableB b
    ON a.username=b.username
GROUP BY a.username