How to list total number of scholarships per department in SQL-CodePudding

I have 2 tables that look like this where I want to query how many scholarships (from Tuition table) each department (from Student table) has distributed:

I am thinking a join is necessary but am not sure how to do so.

CodePudding user response：

Create tables

create table students (
  sid int auto_increment primary key,
  name varchar(100),
  email varchar(100),
  department varchar(100)
);

create table tutions (
  id int auto_increment primary key,
  sid int,
  cost int,
  scholarships int,
  duedate timestamp default current_timestamp
);

Sample data

insert into students (name, email, department)
values
('John Doe', '[email protected]', 'B'),
('Jane Doe', '[email protected]', 'A'),
('Jack Doe', '[email protected]', 'C'),
('Jill Doe', '[email protected]', 'B');

insert into tutions (sid, cost, scholarships)
values
(1, 1000, 2),
(2, 1000, 1),
(3, 1000, 7),
(4, 1000, 2);

Query (department-wise total scholarships)

SELECT department, sum(scholarships) as scholarships
FROM students s
JOIN tutions t ON s.sid = t.sid
GROUP BY department

Output

Running SQL Fiddle

CodePudding user response：

Not sure It's something you want? And not sure scholarships is a number or name of scholarship? So I doubt it's a name as varchar string type.

### dummy record

CREATE TABLE students (
  psu_id INTEGER PRIMARY KEY,
  firstname VARCHAR NOT NULL,
  lastname VARCHAR NOT NULL,
  email VARCHAR NOT NULL,
  department VARCHAR NOT NULL
);
CREATE TABLE tuition (
  tuition_id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  semeter_cost INTEGER NOT NULL,
  scholarships VARCHAR NOT NULL,
  due_date DATE NOT NULL
);

INSERT INTO students VALUES (1, 'John', 'Hello', '[email protected]', 'Engineering');
INSERT INTO students VALUES (2, 'Bella', 'Fuzz', '[email protected]', 'Computer');
INSERT INTO students VALUES (3, 'Sunny', 'World', '[email protected]', 'Science');
INSERT INTO tuition VALUES (1, 1, 4000, 'first_class_en', '2022-05-09' );
INSERT INTO tuition VALUES (2, 2, 3000, 'nobel', '2022-05-09' );
INSERT INTO tuition VALUES (3, 3, 5000, 'hackathon', '2022-05-09' );
INSERT INTO tuition VALUES (4, 1, 4500, 'second_class_en', '2022-05-09' );
-----------------
### query

SELECT s.department, count(t.scholarships)
FROM students s
JOIN tuition t
ON s.psu_id = t.student_id
GROUP BY s.department

### output

department, total_scholarships
Computer|1
Engineering|2
Science|1