derived table

Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.Your syntax looks fine.
Is it not doing what you expect it to?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses whe
re
> program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
to
> do this in a query?
> Please help. Thanks.
>
>
>|||Please post the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.|||I got error:
No column was specified for column 3 of 'DB1'?
what's that mean? Thanks.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
> Your syntax looks fine.
> Is it not doing what you expect it to?
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>|||Is 'mydb' the name of the database you want information about?
program_name refers to the application that is accessing the database.
If so:
The Database is in the field dbid.
Try:
select count(*) from (select hostname, program_name from sysprocesses where
dbid=db_id('mydb') group by hostname, program_name) as DB1
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses
> where program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
> to do this in a query?
> Please help. Thanks.
>
>|||You must have posted a different query than what you are trying to run.
That error is telling you that all columns specified in a derived table need
a name, so that they can be referenced by the outer query.
If you had a query like:
select count(*) from (select hostname, program_name, count(1) from
sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
I'd expect the error. Because the third column does not have a referencable
name for the outer query. It would need to be re-written like:
select count(*) from (select hostname, program_name, count(1) as rowCount
from sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>|||your post is missing the aggregate for DB1, btw
however, this is the problem: all columsn in in a derived table must
have a name, e.g. [using max(spid) for example]
select count(*) from (select hostname, program_name, max(spid) as spid
from sysprocesses where program_name='mydb' group by hostname,
program_name) as DB1
or
select count(*) from (select hostname, program_name, max(spid)from
sysprocesses where program_name='mydb' group by hostname, program_name)
as DB1 (hostname, program_name, spid)
js wrote:
> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>
>|||Thanks Trey.
I got it. need to assign name: count(*) as counters first
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:u3fb5gjFGHA.984@.tk2msftngp13.phx.gbl...
> your post is missing the aggregate for DB1, btw
> however, this is the problem: all columsn in in a derived table must have
> a name, e.g. [using max(spid) for example]
> select count(*) from (select hostname, program_name, max(spid) as spid
> from sysprocesses where program_name='mydb' group by hostname,
> program_name) as DB1
> or
> select count(*) from (select hostname, program_name, max(spid)from
> sysprocesses where program_name='mydb' group by hostname, program_name) as
> DB1 (hostname, program_name, spid)
>
> js wrote:|||Thanks Ryan.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:6026DBD4-29D4-426C-BF58-DCAB853CB8E0@.microsoft.com...
> You must have posted a different query than what you are trying to run.
> That error is telling you that all columns specified in a derived table
> need
> a name, so that they can be referenced by the outer query.
> If you had a query like:
> select count(*) from (select hostname, program_name, count(1) from
> sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> I'd expect the error. Because the third column does not have a
> referencable
> name for the outer query. It would need to be re-written like:
> select count(*) from (select hostname, program_name, count(1) as rowCount
> from sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>sql