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> 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> USE alishaikh
Database changed
mysql> CREATE TABLE student
    -> (
    ->   StudentID CHAR(8) NOT NULL,
    ->   Name VARCHAR(40),
    ->   PRIMARY KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 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');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

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

mysql> INSERT INTO attendance (StudentID) VALUES
    -> ('k1052280'),('k1052280'),('k1052287'),('k1052287'),
    -> ('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> 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`;
+-----------+-------------------+-------+
| 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>

GIVE IT A TRY !!!

StackExchange Database Administrators Q#97180, answer score: 48