CODEWITHSUNDEEP
sql-serverunion
Trent
Trent

Reputation: 1593

Performing a Union on the same Table

I'm sure I'm just searching the wrong query, so feel free to point me to duplicates if so.

I have a table;
Projects
idProject | Name | Number | Status | DateRaised

What I would like to do, is be able to see the number of projects raised, won, lost, etc.

I thought that I could do this with a union, like so;

SELECT
CONVERT(nvarchar, DATEPART(YEAR, DateRaised),107) AS [Year],
COUNT(*) AS [Tenders Received],
'' AS [Tenders Won],
NULL AS [Tenders Lost],
NULL AS [Tenders Pending],
FROM Projects P
GROUP BY DATEPART(YEAR, DateRaised)

UNION

SELECT
CONVERT(nvarchar, DATEPART(YEAR, DateRaised),107) AS [Year],
'' AS [Tenders Received],
COUNT(*) AS [Tenders Won],
NULL AS [Tenders Lost],
NULL AS [Tenders Pending],
FROM Projects P
WHERE P.ProjectStatus = 2
GROUP BY DATEPART(YEAR, DateRaised)

ORDER BY [YEAR] DESC

All I get though, is a table twice the size.
i.e.

2015 | 10   | NULL | NULL | NULL
2015 | NULL | 5    | NULL | NULL

When what I actually want is this; 

2015 | 10 | 5 | NULL | NULL

Perhaps union is the wrong construct. I'm actually at a loss now. I was fairly sure this was how I'd done it before, but without having the previous code to work off, I am now completely lost.

Upvotes: 0

Views: 600

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use conditional aggregation instead of UNION:

SELECT
    DATEPART(YEAR, DateRaised) AS [Year],
    COUNT(*) AS [Tenders Received],
    SUM(CASE WHEN P.ProjectStatus = 2 THEN 1 ELSE 0 END) AS [Tenders Won],
    NULL AS [Tenders Lost],
    NULL AS [Tenders Pending]
FROM Projects P
GROUP BY DATEPART(YEAR, DateRaised)

Additionally, you may remove the CONVERT function on YEAR.

Upvotes: 2

Related Questions