What is the best way to Aggregate query results

[nikos101]

What is the best way to Aggregate query results that are retrived in a ms sql while statement,into a single query result?

[Kronin555]

Use the database aggregate functions so your query only returns one row.

[nikos101]

Yes I am doing that but its returning seperate queries,

declare @month int
set @month = 0
while (@month <12)

BEGIN
select stuff


SET @month=@month + 1
end

I may have to use a temparay table

[-__cfSearching__-]

> I may have to use a temparay table

... or a table variable. What is your loop doing? Is it possible to write the query without a loop?

[nikos101]

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

[-__cfSearching__-]

> 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?

[nikos101]

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

[-__cfSearching__-]

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

[Guest]

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.

[-__cfSearching__-]

> 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.

[-__cfSearching__-]

Duplicate

[nikos101]

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

[nikos101]

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

[nikos101]

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. "

[-__cfSearching__-]

> forget it I figured it all out :)

So, what did you end up doing?

> 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. "

Why is that a bad thing? You should use aliases with joins anyway, unless you like typing out full table names 😉