title: SQL - Count Multiple columns and JOIN in a single query
body: <p>I'm trying to get the count the number of fields (AAA - ZZZ) that have a value other than 'N/A' from a single row to display alongside the basic info. I have 2 queries that separately work find as SQL commands, but I'm looking to merge them together to work in the foreach statement.</p>
  
  <p>Table structure:</p>
  
  <pre><code>ID  UserID  Date    Assignment  AAA BBB CCC DDD
  1   1   1/27/2014   Test    5.25    N/A 4   N/A
  2   4   1/27/2014   Test2   N/A N/A 3.5 2.75
  3   1   1/29/2014   Test3   1.25    N/A N/A 4.5
  </code></pre>
  
  <p>For example, using the info above, the count would be:</p>
  
  <pre><code>ID 1, Count = 3
  ID 2, Count = 2
  ID 3, Count = 1
  </code></pre>
  
  <p>PHP Table Code:</p>
  
  <pre><code>    foreach ($pdo-&gt;query($sql) as $row) {
           echo '&lt;tr&gt;';
           echo '&lt;td&gt;'. $row['Date'] . '&lt;/td&gt;';
           echo '&lt;td&gt;'. $row['UserName'] . '&lt;/td&gt;';
           echo '&lt;td&gt;'. $row['Assignment'] . '&lt;/td&gt;';
           echo '&lt;td&gt;'. $row['Count'] . '&lt;/td&gt;';
           echo '&lt;/tr&gt;';
           }
  }
  </code></pre>
  
  <p>Retrieval query:</p>
  
  <pre><code>$sql = "SELECT db_log.ID, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName, db_log.Date, db_log.Assignment
  FROM `db_log` 
  INNER JOIN `db_users` ON 
  db_log.UserID=db_users.ID 
  ORDER BY `ID` DESC LIMIT 0,20";
  </code></pre>
  
  <p>Alternative query:</p>
  
  <pre><code>$sql = "SELECT db_log.ID, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName, db_log.Date, db_log.Assignment
  FROM `db_log`, `db_users` 
  WHERE db_log.UserID=db_users.ID 
  ORDER BY `ID` DESC LIMIT 0,20";
  </code></pre>
  
  <p>Count Query:
  Note that the <code>ID</code>=1 should be the id from the first query:</p>
  
  <pre><code>SELECT COUNT(AAA) FROM (
  SELECT `AAA` FROM `db_log` WHERE `AAA` &lt;&gt; 'N/A' AND `ID`=1 UNION ALL
  SELECT `BBB` FROM `db_log` WHERE `BBB` &lt;&gt; 'N/A' AND `ID`=1 UNION ALL
  SELECT `CCC` FROM `db_log` WHERE `CCC` &lt;&gt; 'N/A' AND `ID`=1 UNION ALL
  SELECT `DDD` FROM `db_log` WHERE `DDD` &lt;&gt; 'N/A' AND `ID`=1 UNION ALL
  SELECT `EEE` FROM `db_log` WHERE `EEE` &lt;&gt; 'N/A' AND `ID`=1) AS A
  </code></pre>
  
  <p>I've looked into joins, and other workarounds, but to little luck. Thanks in advance for your help :)</p>