Aggregate function for select statement result?

Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.

currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.

Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.

*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales

This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.

thanks,
Tom Anderson
Software Engineer
Custom Business SolutionsWell, i may be able to pull this off, and do an interim call, but i wanted to try to keep it as single sql as possible, seems microsoft even documents that you can't contain a select or aggregate from within an aggregate...

wish that would change, anywho, here is my call on a test database.

SELECT SUM(listprice) FROM tblOrderSales where listprice > 0 GROUP BY [Job Name];
select @.@.rowcount, [Job Name] from tblOrderSales;

man that is simple, but still 2 calls, if anyone knows how to change it to one, i would be VERY much appreciative.|||ok, well, that works well if building a function, procedure, or view, but my vb.net code is only returning the first select statement.

any ideas?|||OK, solved...

Funny how that works, wait long enough, and search hard enough, and eventually you figure out your own problems...

Well, decided to post the full thing here.

Old Code: Implements View for subData

viewShowSales:
SELECT [Shop ID] AS ShopID, SUM(ListPrice) AS Sales
FROM dbo.tblOrderSales
WHERE (ListPrice > 0)
GROUP BY [Shop ID]

Old SelectStatement to make it work:
select sum(viewShopSales.Sales) / count(viewShopSales.Sales) as AvgSales from tblOrderSales c, viewShopSales

New Code: No view needed, and can plop in as a value in any subQuery situation (like i need).

select (Sub1.AvgSales / Sub2.SalesCount) as AvgSales
from
(
select sum(TotalSum) as AvgSales
from (
select sum(ListPrice) TotalSum
from tblOrderSales
where [listprice] > 0
group by [Shop ID]
)as subsub1) as sub1,
(
select count(TotalSum) as SalesCount
from (
select sum(ListPrice) TotalSum
from tblOrderSales
where [listprice] > 0
group by [Shop ID]
)as subsub2
) as sub2

Same Results:
With View
(1 row(s) affected) - 5452283

Without View
(1 row(s) affected) - 5452283

Works great, thanks to this link:
http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspx for showing me to use a derived table!!!