TechGirl - 5 months ago 5
SQL Question

# Compute the average of count result

I am trying to find the average number of products/store. I want something like this:

``````Store       Avg.Products
Store 1     100
Store 2     20
``````

Store table: StoreId

Products table: Productid, Pname, StoreId

If there are 100 total products and by using count i got 9 products for store 1 i want 9/100 for store 1 and similarly for the other stores

I tried:

``````Select avg(counts) from(Select count(*) AS counts
FROM Store Join Product ON Store.Id = product.StoreId Group By Store.id)table1
``````

But this just gives me a single value. Any ideas? Thanks

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.

UPDATE

I've verified the answer and updated the code with a fixed version. Let me know if it works for Your case.

UPDATE II

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
``````
Source (Stackoverflow)