php - Display Count of Collected and Released Blood Components

Answer

-------- id BloodComponent Remarks 1 Whole Blood Collected 2 Platelet Released 3 Plasma Collected 4 Platelet Released 5 Plasma Collected 6 Whole Blood Released|||Name Collected Released

Answer

--------- Whole Blood 1 1 Platelet 0 2 Plasma 2 0

Answer

Solution:

In laravel:

$results = DB::table("BloodCollection")
  ->selectRaw("BloodComponent AS Name,SUM(IF(Remarks = 'Collected', 1, 0)) AS Collected,SUM(IF(Remarks = 'Released', 1, 0)) AS Released")
  ->groupBy("BloodComponent")
  ->get();
Copy code

Or if you already have aBloodCollection model:

$results = BloodCollection::selectRaw("BloodComponent AS Name,SUM(IF(Remarks = 'Collected', 1, 0)) AS Collected,SUM(IF(Remarks = 'Released', 1, 0)) AS Released")
  ->groupBy("BloodComponent")
  ->get();
Copy code

then:

foreach($results as $result){

    echo $results->Name . ": ".$results->Collected ." collected, ".$results->Released ." released.";

}
Copy code

Answer

Solution:

When you require that rows become columns of data you would transform the rows using a crosstab query:

SELECT `BloodComponent` AS `Name`,
SUM(IF(`Remarks` = 'Collected', 1, 0)) AS `Collected`,
SUM(IF(`Remarks` = 'Released', 1, 0)) AS `Released`
FROM `BloodCollection`
GROUP BY `BloodComponent`
Copy code

Here is an informative post showing the use of these kinds of queries in a more detailed fashion.

In Laravel:

DB::table("BloodCollection")
  ->selectRaw("`BloodComponent` AS `Name`,SUM(IF(`Remarks` = 'Collected', 1, 0)) AS `Collected`,SUM(IF(`Remarks` = 'Released', 1, 0)) AS `Released`")
  ->groupBy("BloodComponent")
  ->get();
Copy code