A way around having to loop through results to access more information using the first result set

Question

Given the following Database setup:

Customers
| customerId | accountId | addressId |
|------------|-----------|-----------|
|      1     |    110    |     8     |
|      2     |    120    |     9     |
|      3     |    130    |     10    |

Address
| addressId | companyName | state |
|-----------|-------------|-------|
|     8     |  FooBar Co  |   FL  |
|     9     |   Self Co   |   VA  |
|     10    |    Cli Co   |   CA  |

Tests
| testId | accountId | testType | Status  |
|--------|-----------|----------|---------|
|    1   |    120    |   Urine  |   Done  |
|    2   |    110    |   Blood  | Pending |
|    3   |    110    |   Blood  | Pending |
|    4   |    130    |  Biopsy  |   Done  |
|    5   |    130    |   Blood  |   Done  |
|    6   |    130    |   Urine  | Pending |
|    7   |    110    |  Biopsy  | Pending |
|    8   |    120    |   Urine  | Pending |
|    9   |    110    |  Biopsy  | Pending |
|   10   |    110    |   Urine  | Pending |

Is there a way for me to get around having to loop through a mysqli result set of customers in order to grab the COUNT of each testType based on the name of the type of test and based on the accountId. So for example I am currently doing this:

$sql = "SELECT C.accountId, A.companyName 
         FROM Customers C
         JOIN Address A
         ON C.addressId=A.addressId";

$result = mysqli_query($connection,  $sql);

$customers = mysqli_fetch_all($result, MYSQLI_ASSOC);

foreach( $customers as $customer ) :
  echo '<h2>' . $customer["companyName"] . '</h2>';

  // Urine Tests Count
  $sql = 'SELECT COUNT(testId) as count FROM Tests WHERE accountId=' . $customer["accountId"] . ' AND testType="Urine"';
  $result = mysqli_query($connection, $sql);
  $testCount = mysqli_fetch_array($result, MYSQLI_ASSOC);
  echo '<p>Total Urine Tests: ' . $testCount["count"] . '</p>';

  // Biopsy Tests Count
  $sql = 'SELECT COUNT(testId) as count FROM Tests WHERE accountId=' . $customer["accountId"] . ' AND testType="Biopsy"';
  $result = mysqli_query($connection, $sql);
  $testCount = mysqli_fetch_array($result, MYSQLI_ASSOC);
  echo '<p>Total Biopsy Tests: ' . $testCount["count"] . '</p>';

  // Blood Tests Count
  $sql = 'SELECT COUNT(testId) as count FROM Tests WHERE accountId=' . $customer["accountId"] . ' AND testType="Blood"';
  $result = mysqli_query($connection, $sql);
  $testCount = mysqli_fetch_array($result, MYSQLI_ASSOC);
  echo '<p>Total Blood Tests: ' . $testCount["count"] . '</p>';
endforeach;

As you can probably tell this is hella repetative but so far it's been the only way to get the information I want. I know I could do a COUNT() with a GROUP BY for the tests once I am inside the foreach loop, but if I do that, I won't get back 0 for tests that customer might not have and I need to display if a testType has 0.

I am a lot more well versed in PhP then I am in MySQLi and SQL. Is there something I am missing here? Surely there has to be a better way to get all of this, maybe even get it in one query? Any suggestions or a point in the right direction would be great.

Answer 1

Simple aggregation and a left join or two on your main query should do it. A cross join could be used to identify the unique test types and by cross joining this to a customer/address 1:1 relationship we generate a row for each test type for every customer. That way each customer will have one of each type listed with the appropriate count (0) when no tests of that type exist for an account.

SELECT C.accountId, A.companyName, Z.TestType, Count(distinct T.TestID)
FROM Customers C
LEFT JOIN Address A
  ON C.addressId=A.addressId
CROSS JOIN (Select Distinct TestType from Tests) Z
LEFT JOIN Tests T
  on T.AccountID = C.AccountID
 and Z.TestType = T.TestType
 and ReceiveDT>=SomeDate
 and ReceiveDT<=SomeOtherDate
GROUP BY C.accountId, A.companyName, Z.TestType

LEFT Joinn will return all customer records and only those with address that match and only records from test matching a customer record. So count will be 0 when no type/test match.

Customer:Address:TestTypes:Tests cardinality expected: 1:1:M:M

so if there were only 3 types of tests I would expect to see 3* the number of records in customers in the results. This is because Address and customers appear to be a 1:1 the number of test types will determine how many times we replicate a customer record and the left join to tests is being aggregated so it will not add to the row count.

Obviously you can add a where clause if you need to limit to a specific customer, but I thought you wanted all customers's test type counts for all possible test types.