Embed Select Aggregate in Aggregate Function

[RenEvo]

Having a bit of trouble trying to get the data exactly how i need it. anyone know why sql throws an error on this?

Select count(select sum(sales) from actsales groupby salesdate) from actsales innerjoin ~.....

the error is on the select inside the count, says its invalid.

thanks in advance, i don't want to use a view or stored procedure to do this, trying to break out of a spid calling nightmare that was created long ago.

 

[JarlH]

What are you looking for? To me it looks like you are trying to do:

Select count(DISTINCT salesdates) from actsales innerjoin ~

The "select sum(sales) from actsales groupby salesdate" part will return a result-set consisting of one row for each distinct salesdate.

Then you do "Select count" on this result-set, i.e. you will get the number of distinct salesdate.

 

[RenEvo]

i needed a derived table, as the original data came from a view that had a sum of the sales per day, and i needed to get the count of days that had a non-zero value, then get the avg sales from that to the total sales of all the days queried.

original sql (mockup, not real table):
select sum(viewShopSales.Sales) / count(viewShopSales.Sales) as AvgSales from tblOrderSales c, viewShopSales

Changed to this:
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

thanks for the response though, that does something, but not what i needed