SQL Server Subquery Returning No Records

Question

I have what should be a very simple subquery:

DECLARE @count TABLE (count1 INT, count2 INT)

SELECT
    (SELECT COUNT(*) FROM Bird) AS count1,
    (SELECT COUNT(*) FROM Fish) AS count2
FROM @count

• When I run SELECT COUNT(*) FROM Bird I get 10 back.
• When I run SELECT COUNT(*) FROM Fish I get 5 returned.
• When I run the above query, I get zero records returned.

Am I missing something? I've seen multiple different tutorials and they all say to do pretty much exactly what I am doing.

Answer 1

Since you are selecting records from @count table which does not have any records, you will always get the no records. Btw, what are you trying to achieve?

Answer 2

I assume, that you are trying to insert the two counts into your declared table. If this is true you are missing an INSERT INTO and there is one FROM @count you don't need...

In this example I use two count (on other tables) to insert one row into @count, which you can select in the last step:

declare @count table (count1 int, count2 int);

INSERT INTO @count
select (SELECT COUNT(*) FROM sys.all_columns)
      ,(SELECT COUNT(*) FROM sys.tables);

SELECT * FROM @count;