CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Snake Eyes
Snake Eyes

Reputation: 16764

Show each column for UNION statement in SQL Server 2005

I have these simple SQL statements:

SELECT     COUNT(ID) AS Expr1
FROM         Table1
UNION
SELECT     COUNT(ID) AS Expr2
FROM         Table1 AS Table1_1
WHERE     (UPDATED > CREATED)
UNION
SELECT     COUNT(DISTINCT DATEPART(year, UPDATED)) AS Expr3
FROM         Table1 AS Table1_2

I want to show results as:

Expr1     Expr2    Expr3
-----     -----    -----
5           3         2

How to do that ? I understand that UNION is more reliable and faster than using OR in WHERE clause.

Upvotes: 2

Views: 235

Answers (2)

Fedor Hajdu
Fedor Hajdu

Reputation: 4695

Isn't something like

SELECT 
COUNT(ID) AS Expr1,
    (SELECT COUNT(ID) FROM Table1 WHERE UPDATED > CREATED) as Expr2,
    COUNT(DISTINCT DATEPART(year, UPDATED) as Expr3
FROM Table1

what you need?

Upvotes: 2

marc_s
marc_s

Reputation: 754628

You cannot do this with UNION - that will give you a union of rows - not columns.

For your requirement, try something like this:

SELECT 
    Expr1 = (SELECT COUNT(ID) FROM Table1),
    Expr2 = (SELECT COUNT(ID) FROM Table1 WHERE UPDATED > CREATED),
    Expr3 = (SELECT COUNT(DISTINCT DATEPART(year, UPDATED)) FROM Table1)

That will give you one row with three columns, containing the values you're looking for.

Upvotes: 5

Related Questions