title: mysql select count union
body: <p>I have something like this:</p>
  
  <pre><code>(SELECT COUNT(*) AS total FROM `transactions` WHERE 
     `asset`='u_{$user_id}' GROUP BY id)
   UNION DISTINCT 
  (SELECT COUNT(*) AS total FROM transactions tr 
     INNER JOIN payments pa ON tr.asset = CONCAT('p_', pa.id) 
     WHERE pa.user_id = '{$user_id}' 
     GROUP BY tr.id)
  </code></pre>
  
  <p>It gives <code>1</code></p>
  
  <p>Now works like this:</p>
  
  <pre><code>SELECT
   (SELECT COUNT(*) FROM `transactions` 
      WHERE `asset`='u_{$user_id}')
    + 
   (SELECT COUNT(*) FROM transactions tr 
      INNER JOIN payments pa ON tr.asset = CONCAT('p_', pa.id) 
      WHERE pa.user_id = '{$user_id}')
  </code></pre>
  
  <p>It gives <code>6</code></p>
  
  <p>But i need to get <code>5</code>.., sow how to make a right query?</p>
  
  <p>Sure, i can do this by php, but..HOW by sql..?</p>
  
  <p>Really "and" and "or" conditions does not matter, they works correctly, the problem is in counting UNION`ed query. The second query correctly counts summ (1+5), but values ​​intersect queries. The first one gives result of just first subquery. So, i need to unique results before count...</p>
  
  <p>In php, it should look like that: i get transactions id list by inner join with payments, than construct a long query in a loop, to get something like <code>SELECT COUNT(*) FROM transactions WHERE (*what i have now* OR id=$id_1 OR id=$id_2 OR id=$id_3 etc..)</code></p>
  
  <p><em>UPD: cutted</em></p>
  
  <p><em><strong>RESOLVED!</strong></em>=)</p>
  
  <pre><code>SELECT COUNT(*) FROM(
  SELECT tr.* FROM transactions tr 
  WHERE tr.asset='u_{$user_id}' OR (tr.liability='g' AND tr.l_info='{$user_name}')
  UNION SELECT tr.* FROM transactions tr 
  INNER JOIN payments pa ON tr.asset = CONCAT('p_', pa.id) 
  WHERE pa.user_id = '{$user_id}' AND pa.status='100') 
  AS total
  </code></pre>
  
  <p><strong>AS total</strong> is importantly!</p>