CODEWITHSUNDEEP
sql-serverselectcount
TheyCallMeGreg
TheyCallMeGreg

Reputation: 1

SQL Server - Difficulty with COUNT statement for multiple tables

I'm having difficulty with the statement below. I've tried writing it multiple ways, but receive different errors accordingly. I'd like the result to be output to a single table with each count in a separate (but adjoining) column, like so...

 --------+--------+--------
| alias1 | alias2 | alias3 |
 --------+--------+--------
| count1 | count2 | count3 |
 --------+--------+--------

Method 1:

SELECT COUNT(*) 
FROM AdventureWorks2012.HumanResources.Department AS Alias1
, (SELECT COUNT(*) 
    FROM AdventureWorks2012.HumanResources.Employee) AS Alias2
, (SELECT COUNT(*)  
    FROM AdventureWorks2012.HumanResources.EmployeeDepartmentHistory) AS Alias3

This returns an error on Alias2 (and Alias3) stating, "No column was specified for column 1 of 'Alias2'"

When I then try to rework it as follows:

SELECT COUNT(*) 
FROM AdventureWorks2012.HumanResources.Department AS Alias1
, (SELECT COUNT(*) AS Alias2
    FROM AdventureWorks2012.HumanResources.Employee) 
, (SELECT COUNT(*) AS Alias3
    FROM AdventureWorks2012.HumanResources.EmployeeDepartmentHistory)

The error shifts to the ',' separating the SELECT statements with the error, "Incorrect syntax near ','. Expecting AS, ID, or QUOTED_ID."

What I'm attempting to do seems simple enough, but I'm coming up empty. Any help would be greatly appreciated.

Upvotes: 0

Views: 55

Answers (2)

jpw
jpw

Reputation: 44921

One way to get the result you want would be to use each statement as a derived table: 

SELECT * FROM 
(SELECT COUNT(*) AS Alias1 FROM AdventureWorks2012.HumanResources.Department) AS a1 , 
(SELECT COUNT(*) AS Alias2 FROM AdventureWorks2012.HumanResources.Employee) AS a2, 
(SELECT COUNT(*) AS Alias3 FROM AdventureWorks2012.HumanResources.EmployeeDepartmentHistory) AS a3

(Or you could use the statements as subqueries as shown in another answer)

Upvotes: 0

Anton
Anton

Reputation: 2882

SELECT 
    (SELECT COUNT(*) FROM AdventureWorks2012.HumanResources.Department)  AS Alias1, 
    (SELECT COUNT(*) FROM AdventureWorks2012.HumanResources.Employee) AS Alias2, 
    (SELECT COUNT(*) FROM AdventureWorks2012.HumanResources.EmployeeDepartmentHistory) AS Alias3

Upvotes: 1

Related Questions