Demo Namex Demo Namex - 4 months ago 14
MySQL Question

Add a temporary column in mysql who's entries are counts of same entries of a table

So I have this database which contains in it a list of files. Files can be updated, but the previous versions are still held on to in case of rollbacks. What I want to know is how would I add a temporary column that contains the number of previous versions for that file.

Essentially, how would I crate a temporary column in mysql who's entries depend on something within the main table?

CREATE TABLE Files(
file_id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
student_id INTEGER,
group_id INTEGER,
submission_number INTEGER,
submission_type VARCHAR(10),
FOREIGN KEY (student_id) REFERENCES Student (student_id) ON DELETE CASCADE,
FOREIGN KEY (group_id) REFERENCES Groups(group_id)ON DELETE CASCADE
);


A submission is defined by its submission_number and submission_type. So submission_type 0 would be an assignment, 1 would be a project, 2 would be a quiz and so on. submission_number would be which assignment of that submission type we are uploading. So for example, the third quiz will have a submission_type =2 and a submission_number = 3. we can update a file, so we can upload a new file with the same submission_type and submission_number. What I want to return is a table which contains the number of times each submission had an upload.

So if i had the following table

+---------+------------+----------+-------------------+-----------------+
| file_id | student_id | group_id | submission_number | submission_type |
+---------+------------+----------+-------------------+-----------------+
| 1 | 10049 | 1 | 1 | assignment |
| 2 | 10032 | 1 | 1 | assingment |
| 3 | 10032 | 1 | 2 | quiz |
| 4 | 10032 | 1 | 3 | assingment |
+---------+------------+----------+-------------------+-----------------+


I would want to return the table

+-------------------+-----------------+-------+
| submission_number | submission_type | count |
+-------------------+-----------------+-------+
| 1 | assignment | 2 |
| 2 | quiz | 1 |
| 3 | assignment | 1 |
+-------------------+-----------------+-------+


Another way to think about it, if submission_number and submission_type were coordinates instead, say x,y then i want to count the number of times the same point appears in the table. So the point (1,assignment) appeared twice thus the count is 2.

Answer

You don't need another table just to do that. Just write a query using GROUP BY.

SELECT submission_number, submission_type, COUNT(file_id) FROM Files GROUP BY submission_number, submission_type;

working demo

Comments