SQL - Count Multiple columns and JOIN in a single query

Question

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.

Table structure:

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

For example, using the info above, the count would be:

ID 1, Count = 3
ID 2, Count = 2
ID 3, Count = 1

PHP Table Code:

    foreach ($pdo->query($sql) as $row) {
         echo '<tr>';
         echo '<td>'. $row['Date'] . '</td>';
         echo '<td>'. $row['UserName'] . '</td>';
         echo '<td>'. $row['Assignment'] . '</td>';
         echo '<td>'. $row['Count'] . '</td>';
         echo '</tr>';
         }
}

Retrieval query:

$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";

Alternative query:

$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";

Count Query: Note that the ID=1 should be the id from the first query:

SELECT COUNT(AAA) FROM (
SELECT `AAA` FROM `db_log` WHERE `AAA` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `BBB` FROM `db_log` WHERE `BBB` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `CCC` FROM `db_log` WHERE `CCC` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `DDD` FROM `db_log` WHERE `DDD` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `EEE` FROM `db_log` WHERE `EEE` <> 'N/A' AND `ID`=1) AS A

I've looked into joins, and other workarounds, but to little luck. Thanks in advance for your help :)

You need a to create a [pivot query](http://stackoverflow.com/a/7675121/2589202). [This is a really good tutorial](http://en.wikibooks.org/wiki/MySQL/Pivot_table) on them as well. — crthompson, Mar 12 '14 at 02:21
@ChristianMark What would route would you suggest if you were to do it in PHP? I know you can unset all the 'N/A' values, but how would you count all the other matching columns? — Bob, Mar 12 '14 at 04:18

score 1 · Accepted Answer · answered Mar 12 '14 at 02:57

1

The following should get you the count of non-N/A fields in a single query.

   SELECT id, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName,
    (IF (aaa = 'N/A', 0, 1) + 
     IF (bbb = 'N/A', 0, 1) + 
     IF (ccc = 'N/A', 0, 1) + 
     IF (ddd = 'N/A', 0, 1) ) AS count
    FROM db_log
    JOIN db_users on db_log.userId = db_users.userId

I set up a SqlFiddle to play with this a bit here: http://sqlfiddle.com/#!2/d6990/5

answered Mar 12 '14 at 02:57

Mike Parkhill

5,511
1
28
38

Worked excellent, thank you so much! A true maven of SQL - I'd upvote you in a heartbeat if I had the rep :) – Bob Mar 12 '14 at 04:59

SQL - Count Multiple columns and JOIN in a single query

1 Answers1