MySQL Count rows from another table for each record in table SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID


I am trying to count the last row but instead it counts all the results and returns one result

I am getting something like 



as there are multiple records because there are two entries in Attendance ID for K1052280 I want to count those and return the number. Something like
You are just missing the GROUP BY

YOUR QUERY WITH GROUP BY

SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID,student.`Name`;


SAMPLE DATA

DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
USE alishaikh
CREATE TABLE student
(
  StudentID CHAR(8) NOT NULL,
  Name VARCHAR(40),
  PRIMARY KEY (StudentID)
);
INSERT INTO student (StudentID,Name) VALUES
('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
('k1052299','Troy Fulton');
CREATE TABLE attendance
(
  AttendanceID INT NOT NULL AUTO_INCREMENT,
  StudentID CHAR(8) NOT NULL,
  PRIMARY KEY (AttendanceID),
  KEY (StudentID)
);
INSERT INTO attendance (StudentID) VALUES
('k1052280'),('k1052280'),('k1052287'),('k1052287'),
('k1052288'),('k1052295'),('k1052295'),('k1052295');


SAMPLE DATA LOADED

mysql&gt; DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
Query OK, 2 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
mysql&gt; USE alishaikh
Database changed
mysql&gt; CREATE TABLE student
    -&gt; (
    -&gt;   StudentID CHAR(8) NOT NULL,
    -&gt;   Name VARCHAR(40),
    -&gt;   PRIMARY KEY (StudentID)
    -&gt; );
Query OK, 0 rows affected (0.03 sec)

mysql&gt; INSERT INTO student (StudentID,Name) VALUES
    -&gt; ('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
    -&gt; ('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
    -&gt; ('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
    -&gt; ('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
    -&gt; ('k1052299','Troy Fulton');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql&gt; CREATE TABLE attendance
    -&gt; (
    -&gt;   AttendanceID INT NOT NULL AUTO_INCREMENT,
    -&gt;   StudentID CHAR(8) NOT NULL,
    -&gt;   PRIMARY KEY (AttendanceID),
    -&gt;   KEY (StudentID)
    -&gt; );
Query OK, 0 rows affected (0.02 sec)

mysql&gt; INSERT INTO attendance (StudentID) VALUES
    -&gt; ('k1052280'),('k1052280'),('k1052287'),('k1052287'),
    -&gt; ('k1052288'),('k1052295'),('k1052295'),('k1052295');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0


YOUR QUERY WITH GROUP BY EXECUTED

mysql&gt; SELECT
    -&gt;   student.StudentID,
    -&gt;   student.`Name`,
    -&gt;   COUNT(attendance.AttendanceID) AS Total
    -&gt; FROM
    -&gt;   student
    -&gt; LEFT JOIN attendance ON student.StudentID = attendance.StudentID
    -&gt; GROUP BY student.StudentID,student.`Name`;
+-----------+-------------------+-------+
| StudentID | Name              | Total |
+-----------+-------------------+-------+
| k1052280  | Ali Shaikh        |     2 |
| k1052287  | McKenzie Roth     |     2 |
| k1052288  | Dacey Sullivan    |     1 |
| k1052294  | Zelda Cantu       |     0 |
| k1052295  | Kimberly Melton   |     3 |
| k1052296  | Tatianna Cantrell |     0 |
| k1052297  | Morgan Thornton   |     0 |
| k1052298  | Allistair Barlow  |     0 |
| k1052299  | Troy Fulton       |     0 |
+-----------+-------------------+-------+
9 rows in set (0.00 sec)

mysql&gt;


GIVE IT A TRY !!!