asked
How to take the avegare of a count function?
I am trying to find the average numbers of users that use specific product for a specific month Users can be in different countries. I want to GROUP BY Country There is a column named ACCOUNT and each ACCOUNT can have a Number of USERIDS. USERIDs can be in different countrues. Each userid uses a PRODUCTID. This is a sample ACCOUNT USERID COUNTRY PRODUCTID 1234 1 US 12 1234 1 US 11 1234 3 FR 11 4321 2 US 12 4321 4 GER 12 I think a need a count then to take an average but am having problems. SELECT count( s.userid ) , c.ProductID ,c.yearmnth ,s.Country FROM Sites as s join Usage as c on s.USERID = c.USERID where c.YearMnth = 201101 group by c.ProductID ,c.yearmonth ,s.Country order by s.Country
One possible way is to use a Common Table Expression (CTE). It would look something like this: ; WITH AvgOfCountCTE (CountUser, ProdID, YearMonth, Country) AS ( SELECT count( s.userid ) , c.ProductID ,c.yearmnth ,s.Country FROM Sites as s join Usage as c on s.USERID = c.USERID where c.YearMnth = 201101 group by c.ProductID ,c.yearmonth ,s.Country ) SELECT AVG(CountUser), ProdID, YearMonth, Country FROM AvgOfCountCTE GROUP BY ProdID, YearMonth, Country
group by product and count user id then nest this into a query where you average the cntUserid like this: select AVG(cntUserId), Product FROM (select count(userid), product from tblx group by product) tbly group by product
