Select SubSelect in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select SubSelect in SQL

Author

Topic

richxyz
Starting Member

12 Posts

Posted - 2007-09-12 : 15:26:38

I thought I was able to do this in SQL - but maybe it was in Oracle...

I want to have a SELECT statement within my SELECT statement, and treat the sub select as if it were a table/view. I could do this by compiling a view with this SELECT, but want to do it without.

My subselect is

select userid, count(*) countlogin
from tUserLogin
group by userid

Now I want to get the counts of the countlogins, so that I know how many single logins do we have, how many with 2 logins, how many with 3 logins, etc.

so I want to do something like this, if

select countlogin, count(*)
from ([select statement above])
group by countlogin
order by 2

When I try this, SQL Server gives the error
Incorrect syntax near the keyword 'group'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-12 : 15:31:36

This is called a derived table. It must be aliased. I used t for the alias name in the code below.

select countlogin, count(*)
from
(select userid, count(*) countlogin
from tUserLogin
group by userid) t
group by countlogin
order by 2

It seems to me that you can accomplish this with a HAVING clause rather than a derived table though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

richxyz
Starting Member

12 Posts

Posted - 2007-09-19 : 11:49:27

Thank you. It works great!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-09-19 : 12:57:51

As an aside , in SQL 2005 , you can use Common Table Expressions

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

stgamba
Starting Member

2 Posts

Posted - 2010-02-05 : 10:23:06

I am having a similar problem.

Suppose I have a table sg defined with one column eventtime which is a datetime.

I want to get the average of the count of rows if I group them by hour, across a month.

I can get a sum or row counts, but not the average because of syntax errors.

This works for a sum of rows.
select count(*), convert(char(2),EventTime,108)
from sg
group by convert(char(2),EventTime,108)

How do I get the average of the sum, grouped by hour?
I can provide data if needed.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 10:29:32

quote:
Originally posted by stgamba

I am having a similar problem.

Suppose I have a table sg defined with one column eventtime which is a datetime.

I want to get the average of the count of rows if I group them by hour, across a month.

I can get a sum or row counts, but not the average because of syntax errors.

This works for a sum of rows.
select count(*), convert(char(2),EventTime,108)
from sg
group by convert(char(2),EventTime,108)

How do I get the average of the sum, grouped by hour?
I can provide data if needed.

Try

select AVG(counting) as counting from
(
select count(*) as counting, convert(char(2),EventTime,108) as EventTime
from sg
group by convert(char(2),EventTime,108)
) as t

Madhivanan

Failing to plan is Planning to fail

stgamba
Starting Member

2 Posts

Posted - 2010-02-05 : 11:59:47

I did a workaround with another table. I need to include the date and hour with the average.

Thanks for replying.

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources