question

sqlLearner 1 avatar image
sqlLearner 1 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
sqlaggregatescounting
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

grrl_geek avatar image
grrl_geek answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 avatar image
Slick84 answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.