I am trying to find the average number of products/store. I want something like this:
Store 1 100
Store 2 20
Select avg(counts) from(Select count(*) AS counts
FROM Store Join Product ON Store.Id = product.StoreId Group By Store.id)table1
You can try to group the outer query by store name and cast
AVG parameter to
float to allow it to return non-integer values:
SELECT storeName, avg(CAST(counts AS float)) FROM (Select count(*) AS counts, S.Name as storeName FROM Store S JOIN Product P ON S.Id = P.StoreId Group By S.id, S.Name) table1 GROUP BY storeName
It may be not the best way to do this, but it should return the expected results.
I've verified the answer and updated the code with a fixed version. Let me know if it works for Your case.
According to Your comment, You would like to calculate the number of products for each shop name and then divide it by total number of products in the database. If this has to be one query, You can do it like this:
SELECT S.Name as storeName, CAST(count(*) AS float) / MIN(T.TotalCount) AS AVGToTotalProductsCount FROM Store S JOIN Product P ON S.Id = P.StoreId JOIN (SELECT COUNT(1) AS TotalCount FROM Product) T ON 1=1 GROUP BY S.Name
However, if the query can be split into multiple queries, You may be able to calculate it like this:
DECLARE @totalNumOfProducts INT SELECT @totalNumOfProducts = COUNT(1) FROM Product SELECT S.Name as storeName, CAST(COUNT(*) AS float) / @totalNumOfProducts AS AVGToTotalProductsCount FROM Store S JOIN Product P ON S.Id = P.StoreId GROUP BY S.Name