SQL Union returns multiple line items for same Column

Transact-SQL
1

The following union statement is creating seperate line items for the same year. I would
like to have distinc years with all the info on a single line instead on multiple entries.
I also tried using row_numder , and dense rank however the same issue

Select
year,
count(claims),
null
From Employees

UNION

Select
year
NULL,
count(claims2)
From Employees

RETURNS

2001 2, null
2001 null, 4
2002 4,null
2003, null,1
2003,1,null

I would like everything on a single line

2001,2,4
2002,4,null
2003,1,1

2 
SELECT
	year,
	COUNT(claims) AS ClaimsCount,
	COUNT(claims2) AS Claims2Count
FROM
	Employees
GROUP BY
	year;

Using COUNT function will count the number of rows for which the value inside the COUNT function is not NULL. Is that what you want?

3

My apologies. I forgot to add that that there are different where clause for each SELECT

Select
e.year,
count(e.claims),
null
From Employees e
inner join department d
on e.emp_id = d.emp_id
and d.location in ('canada','africa')
WHERE e.CLAIMS IN (5,6,7,8)
group by e.year

UNION

Select
e.year
NULL,
count(e.claims2)
From Employees e
inner join departments d on e.emp_id = d.emp_id
and d.location in ('usa','england')
WHERE e.claims2 IN (10,11,12)
group by e.year

RETURNS

2001 2, null
2001 null, 4
2002 4,null
2003, null,1
2003,1,null

I would like everything ona single line

2001,2,4
2002,4,null
2003,1,1

4

If Department and Departments are two different tables, you are better off keeping your query as it is and then pivoting the results. If it is a typo, and the two are the same table, then you could do the following:

SELECT
	e.year,
	SUM( 
		CASE
			WHEN d.location IN ( 'canada', 'africa' ) AND e.CLAIMS IN ( 5, 6, 7, 8 ) THEN 1 
			ELSE 0 
		END) AS Claims,
	SUM( 
		CASE 
			WHEN d.location IN ( 'usa', 'england' ) AND e.CLAIMS2 IN ( 10, 11, 12 ) THEN 1 
			ELSE 0 
		END) AS Claims2
FROM
	Employees e
    INNER JOIN department d ON e.emp_id = d.emp_id
-- you might want to add a where clause here if there are other locations, claims types, claims2 types
GROUP BY 
	e.year;
5

Something like this perhaps?

SELECT Col1, Col2, SUM(MyCount) AS MyCount
FROM
(
    SELECT Col1, Col2, COUNT(Col3) AS MyCount
    FROM TableA
    UNION
    SELECT Col1, Col2, COUNT(Col3) AS MyCount
    FROM TableB
) AS X
GROUP BY Col1, Col2
6

Thanks a lot kristen this is what i did initially i messed up on the case statement.
however this is EXACTLY WHAT i needed THANK YOU for the clarification