Array Data Calculation

Question

I have met problem dealing with multidimensional array. Below are the code to store all the data query from mysql to array. May I know is there any idea how to calculate how many different module name in the multidimensional array and break the status into 2 column which are late and present? The SQL query get the data from a complex table which store all ModuleName and different status. I need to categories them and show in a table. So basically I need to check how many module in the array and how many late or present for the module.

while($array = mysql_fetch_array($result)){
        $list[] = array(
                        "ModuleName"=>$array['ModuleName'],
                        "status"    =>$array['Status']
            ); 
    }

Sample of the SQL query

This is the sample of expected result

Sample Expected Result

So, you want the output to be a single row with 3 columns (module count, late, present)? Can you share the SQL to retrieve the data and the underlying data structure? I'm guessing this can probably be easily performed in your SQL query. — gwc
– gwc, Commented Sep 10, 2013 at 16:40
@MarcB This don't work as I need according to the Module Name to get how many late and how many present. — Carson Lee
– Carson Lee, Commented Sep 10, 2013 at 16:45
@MarcB Not working bro ~ The status is different ~ How u gonna count if the array store different of value? It will just get the same row I get from SQL — Carson Lee
– Carson Lee, Commented Sep 10, 2013 at 16:51

gwc · Accepted Answer · 2013-09-10 16:57:06Z

1

Updated to group by ModuleName

SELECT ModuleName
      ,COUNT(*) AS module_count
      ,SUM(CASE WHEN Status != 'Present' THEN 1 ELSE 0 END) as late_count
      ,SUM(CASE WHEN Status  = 'Present' THEN 1 ELSE 0 END) as present_count
  FROM ( your original query )
 GROUP BY ModuleName

Just replace "your original query" with the SQL you used to retrieve the data in the array.

Original

SELECT COUNT(*) AS module_count
      ,SUM(CASE WHEN Status != 'present' THEN 1 ELSE 0 END) as late_count
      ,SUM(CASE WHEN Status  = 'present' THEN 1 ELSE 0 END) as present_count
  FROM ( your original query )

edited Sep 10, 2013 at 16:57

answered Sep 10, 2013 at 16:44

gwc

1,2937 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Carson Lee Over a year ago

great to c this. Anyway, i have used this sql query to pull the data. Cause this database design is quite a mess. SO lastly i joined some of the table to get all the info. SELECT * , a.AttendanceID AS id FROM classes AS c, attendances AS a, incharges AS i WHERE a.StudentID = "TP000001" AND a.ClassID = c.ClassID AND c.InchargeID = i.InchargeID LIMIT 0 , 30

Carson Lee Over a year ago

Thanks so much. It help a lot. Is there any way to filter the module and filter the status at the same time? =D If I didn't get the wrong information, currently the sql is filter the status from the same module, am i correct?

gwc Over a year ago

Not quite sure what you're asking for. It would help a lot if you would 1) show sample data, 2) show expected results, 3) show your current SQL.

Carson Lee Over a year ago

I would like to know the ModuleName should replace with the picture I show above "ModuleName" right? the module_count, late_count and present_count are the variable to store the number of module, number of late and number of present? The question is the sql query i show in earlier comment, i pull all the module and attendance related to the student. So the data might appear to have different of module name and different of status.

Collectives™ on Stack Overflow

Array Data Calculation

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related