Is it possible to achieve this referencing?

1

Hello

Is it possible to create this:

SELECT COUNT COL1 FROM (SELECT ... AS TABLE1)

So that I can display the count of the above but also use the nested SELECT in subsequent queries?

I am not clear on how to reference the nested SELECT.

Thanks!

2

Nope - not possible. The derived table is only available in that query - it doesn't exist outside the scope of that query.

3

Isn't there a workaround?

E.g. can I do:

SELECT ... AS TABLE1
SELECT COUNT ... FROM TABLE1

4

Hard to tell exactly what you're trying to do.

But if you just want to get a total row count along with detail rows, you can do something like this:

SELECT COUNT(object_id) OVER() AS total_table_count, * 
FROM sys.tables
5

A workaround for what - exactly? You asked if it was possible to use a derived table - in another query, which is not possible.

6

On a slightly different note, I have two selections which I want to reuse in multiple queries in a way of filter i.e. WHERE COL1 IN (SELECT ...)

What is the neater way to reference them? I am not sure how it works, is it:
SELECT * FROM TABLE1 WHERE COL2=1 END AS @data1 ?

I want to reuse this list, ie in:
SELECT * FROM TABLE2 WHERE COL3 NOT IN @data1

7

If you want to use aggregated results more than once, you can always drop those results into a Temp Table.

8

As already stated - to do that you will need a temp table, table variable, function or view. If this 'list' will be used for multiple queries - in multiple "files/scripts" then either a function or view would work.

If this 'list' will only be used in the current file - then a temp table or table variable will work.

9

Thanks, any reference on temp table vs table variable?

I read about table variable but not sure on on temp table.

Can I simply do:
DECLARE @mytable table (SELECT * FROM TABLE1) ?

10

No - for a table variable it is basically the same as creating a table (or temp table):

CREATE TABLE #MyTable ( {list of columns for the table} );
DECLARE @myTable TABLE ( {list of columns for the table} );

For what you are asking - you can use INTO to build a temp table from a query:

SELECT ...
  INTO #myTable
  FROM dbo.Table1
 WHERE ...

There are a lot of discussions around using table variables vs temp tables - and when one is better over the other. You can search for those conversations and review those discussions.

11

Thanks, that's excellent, it's as neat as I hoped. One last question, does the table variable above, need to be as #myTable (i.e. in #... format) or in @.... format or simply in a mytable format?

EDIT: Oh wait, do I simply go on writing SELECT * INTO mytable or I need to create and then declare the mytable first?

If the latter, I will need to learn how to create and declare tables.

12

I don't even know how to answer this...this information is all available in the help for SQL Server - referred to as BOL (Books Online). I would recommend that you take at least a few minutes to review the documentation on SELECT and FROM - then come back with further questions if needed.

13

This should help.
https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver15

2 Likes
14

we are more than happy to answer some questions but it might better for you to take a full course in SQL server.

1 Like