title: Correlated subqueries in MySql - how to evaluate outer query first?
body: <p>I am having problems with subqueries in MySql. I have a table containing user groups. The columns are id, name and the properties with a comment describing each row:
  (Id is INT, Name VARCHAR, all other TINYINT(1) (boolean that is)</p>
  
  <pre><code>ID   |   Name   |   login   |   post   |   manage
   1     user           1           0           0
   2     poster         1           1           0
   3     admin          1           1           1
  </code></pre>
  
  <p>My goal is to be able to list the usergroup properties (login, post and manage above) and the number of usergroups that has each property (3, 2 and 1 respectively).</p>
  
  <p>This query works (but obviously counts the login column every time):</p>
  
  <pre><code>SELECT @colname:=cols.column_name,cols.column_comment,
    (SELECT COUNT(*) FROM db.usergroups WHERE login=1) AS num_users
  FROM information_schema.columns AS cols 
  WHERE TABLE_SCHEMA='db' AND TABLE_NAME='usergroups' AND column_type='tinyint(1)';
  </code></pre>
  
  <p>This does not work (num_users is always 0)</p>
  
  <pre><code>SELECT @colname:=cols.column_name,cols.column_comment,
    (SELECT COUNT(*) FROM db.usergroups WHERE cols.column_name=1) AS num_users
  FROM information_schema.columns AS cols 
  WHERE TABLE_SCHEMA='db' AND TABLE_NAME='usergroups' AND column_type='tinyint(1)';
  </code></pre>
  
  <p>This neither (num_users is always 0)</p>
  
  <pre><code>SELECT @colname:=cols.column_name,cols.column_comment,
    (SELECT COUNT(*) FROM db.usergroups WHERE @colname=1) AS num_users
  FROM information_schema.columns AS cols 
  WHERE TABLE_SCHEMA='db' AND TABLE_NAME='usergroups' AND column_type='tinyint(1)';
  </code></pre>
  
  <p>Is there any way to get this to work? That is - to evaluate the outer statement first?</p>
  
  <p>-</p>
  
  <p>Thanks a lot for any help!</p>
  
  <p>/Victor</p>