What is the best way to Aggregate query results

One of the things I don't like about table variables is

"Unlike ordinary tables, table variables can be used in join operator only by specifying alias name. "

forget it I figured it all out :)
God bless you all :)

If you have select statement with subqueries is it possible to do a join also?

select tb3.col1 (subquery1),(subquery2),(subquery3) left outer join table3 as tb3

this doesn't work as you need a "from table" statement before left outer join

Duplicate

> select month01=(select count(*) from table where month = 1),
> month02=(select count(*) from table where month = 2), etc

As subqueries can be often be slower than an equivalent join, I would investigate whether it is actually needed. People sometimes use subqueries because they do not know how to write an equivalent join.

But it is hard to recommend anything concrete with the scant details provided.

How about

select month01=(select count(*) from table where month = 1),
month02=(select count(*) from table where month = 2), etc

You could probably do a join with a table containing rows with simply 1 thru 12 in the only column too.

It is difficult to make accurate recommendations without knowing the structure.

> select ( (select aggregate from table1),select column from table2
> I don't think this works

That would be a subquery.
http://msdn.microsoft.com/en-us/library/ms189575.aspx

However, joins (inner or outer) are often a better option in terms of performance. Joins can also be used with derived tables.

http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

I can't really go into detail with the table.

Can you select

something like this

select ( (select aggregate from table1),select column from table2

I don't think this works

> must be in a loop, the loop gathers data for each month,
> which may not have any data

Not necessarily. It depends. What is the structure of your data and what does the loop achieve that cannot be done with a JOIN?

must be in a loop, the loop gathers data for each month, which may not have any data