TechGirl TechGirl - 6 months ago 17
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

Answer

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