1

Simple SQL query:

(select e.lastName as Name, count(o.orderid) as numOrd from
Employees e join Orders o on e.employeeid=o.employeeid
group by e.lastName)

and result

Buchanan 42
Callahan 104
Davolio 123
Dodsworth 43

My question is how to achieve in SQL something like that:

let queryResult = 
(select e.lastName as Name, count(o.orderid) as numOrd from
Employees e join Orders o on e.employeeid=o.employeeid
group by e.lastName)

and after that to write something like this, which will be the output:

select AVG(qr.numOrd) from queryResult qr

Is it possible without creating any new tables?

Pavel
  • 431
  • 1
  • 6
  • 17
  • Sure, check table variables. – dean Apr 26 '14 at 20:20
  • Why not just a CTE? These works well for "aliases" and allow the query planner to optimize the entire operation. Otherwise, a [SELECT INTO tv](http://stackoverflow.com/questions/3838240/select-into-a-table-variable-in-t-sql) can be used if the results need to be forced, for whatever reason. – user2864740 Apr 26 '14 at 20:24

3 Answers3

2

yes, but why not just something like:

select count(o.orderid) / count (distinct e.employeeid) AvgNumOrder
  from orders

Derived tables, CTEs, Subqueries, Temp tables, tables variable all do what you ask, but none are needed.

Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
0

You might want to use a Common Table Expression (CTE).

rory.ap
  • 34,009
  • 10
  • 83
  • 174
0

Look at the SQL Server Views

You can define view as

create view queryResult as
select e.lastName as Name, count(o.orderid) as numOrd from
Employees e join Orders o on e.employeeid=o.employeeid
group by e.lastName;
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68