SQL query optimization. Select data from different tables?

Question

MySQL

Welcome all. I can not understand in what ways it is possible to make a request differently.
I have a query like this:

SELECT			  	
   sum(state = 1) as yes,
   sum(state = 0) as no
          
FROM pay_partners WHERE autor_signal = :autor

UNION SELECT 
         sum(buy_follow),
  sum(money_buy_sub)

 FROM partners_all WHERE autor = :autor
               	
UNION SELECT  avg(count_symbols), avg(count_symbols) FROM article WHERE autor = :autor

 UNION SELECT rating, rating FROM users WHERE login = :autor

where :autor is the username of the author. and reusing the selection of columns in the last two queries, it's just like an improvised placeholder, since sql, when using JOINs, starts scrolling columns several times, and all my counr, avg, max functions just show twisted data.

Is it possible to do without duplicate improvised placeholders?
in the last two unions, duplicate data is given, since sql gives an error if the number of columns is different, so I added duplicates in the form of placeholders.

How can you do it faster? Due to my inexperience, I still don't understand. thanks for answers!!!

Answer 1

the last 2 lines can be changed like this:

UNION SELECT 0, 0 FROM article WHERE autor = :autor
UNION SELECT 0, 0 FROM users WHERE login = :autor

I understand that the last 2 tables are needed to count rows?