By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,505 Members | 1,850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,505 IT Pros & Developers. It's quick & easy.

Common Table Expression?

P: n/a
What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston

Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"N. Shamsundar" <sh******************@nospam.xyz> wrote in message
news:c4***********@masala.cc.uh.edu...
What is the SQL Server equivalent of DB2 common table expressions? For
example,

with gry(year,count) as(
select floor(sem/10),count(distinct ssn)
from grades
group by floor(sem/10)
)
select year,sum(count) Head_Count from gry
group by year
having year >= 1980;

N. Shamsundar
University of Houston


If you have a lot of queries which will reference the CTE, you could create
a view or table-valued function. If you only have a few queries, or if you
can't create a view or function for some reason, then a derived table is
probably the only other alternative. Your example seems to be relatively
simple, so I guess any of these options will work, but in more complex cases
a function might give you the most flexibility. CTEs will be in Yukon, by
the way.

Simon
Jul 20 '05 #2

P: n/a
something like this:

*/ Untested! */
select
a.year,
a.sum(amount)

from
(select floor(sem/10) as year ,count(distinct ssn) as amount
from grades
group by floor(sem/10)
) as a

where
year >=1980

group by a.year
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.