How to remove subquery from FROM field in MySQL

Question

I am new to MySQL and databases, and I have seen in many places that it is not considered good programming practice to use subqueries in the FROM SELECT field in MySQL, for example:

select userid, avg(num_pages) from (select userid , pageid , regid , count(regid) as num_pages from reg_pag where ativa = 1 group by userid, pageid) as from_query group by userid; 

which calculates the average number of registers on a page that users have.

The reg_page table looks like this:

Questions:

• How to change a query so that it does not use a subquery in the FROM field?

• And is there a general way to smooth out queries like this?

Answer 1

The average number of registers per page for each user can also be calculated as the number of registers per user divided by the number of pages per user. Use a counter to count only individual psgeids for each user:

 select userid, count(regid) / count(distinct pageid) as avg_regs from reg_pag where ativa=1 group by userid; 

There is no general way to smooth such queries. It may not even be possible to smooth out some of them, otherwise it would make little sense to have this function in the first place. Don't be afraid to use subqueries in the from clause, in some cases they can be even more efficient than a flattened query. But this is a manufacturer and even a version.

Answer 2

One way is to use count(distinct) :

 select userid, count(*) / count(distinct pageid) from reg_pag where ativa = 1 group by userid;