MYSQL sum() for distinct rows with left join

i have three table:

DROP TABLE IF EXISTS `college_details`;

CREATE TABLE `college_details` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`student_id` int(10) DEFAULT NULL,
`collegename` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Data for the table `college_details` */

insert into `college_details`(`id`,`student_id`,`collegename`) values (1,1,'MMMEC,GKP'),(2,1,'MMMTU,GKP'),(3,2,'LPUT,JAL'),(4,3,'LINGAYS');

/*Table structure for table `course_details` */

DROP TABLE IF EXISTS `course_details`;

CREATE TABLE `course_details` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`student_id` int(10) DEFAULT NULL,
`fee` int(10) DEFAULT NULL,
`coursename` varchar(400) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `course_details` */

insert into `course_details`(`id`,`student_id`,`fee`,`coursename`) values (1,1,25,'Linux'),(2,2,25,'php'),(3,3,205,'php6');

/*Table structure for table `student_details` */

DROP TABLE IF EXISTS `student_details`;

CREATE TABLE `student_details` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) DEFAULT NULL,
`SEX` varchar(1) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `student_details` */

insert into `student_details`(`ID`,`NAME`,`SEX`) values (1,'Ashish','m'),(2,'harpreet','m'),(3,'tanuj','m');

Relation among them :

Student  —– > course — > college  (1:n:p) (one to many relation)

one student can enroll for ‘n’ course and ‘p’ college

Query:

SELECT * FROM `student_details` AS student LEFT JOIN course_details AS course ON course.student_id = student.id
LEFT JOIN college_details AS college ON college.student_id = student.id WHERE student.id=1;

i am getting this type query result:

ID | Name | sex | Ids_course | Fee | Coursename | Ids_college | College_name

1   | Ashish | Male | 1 | 25 | Linux certification | 1 | MMMEC, GKP

1   | Ashish | Male | 1 | 25 | Linux certification | 2 | MMMTU, GKP

if i add “SUM” method in query

SELECT  SUM(course.fee) as TOTAL  FROM `student_details` AS student LEFT JOIN course_details AS course ON course.student_id = student.id
LEFT JOIN college_details AS college ON college.student_id = student.id WHERE student.id=1;

after left join and apply sum method on Fee column you will get incorrect result since Student ashish with having ID ‘1’ enroll only for one course and two different college :

you will get result:
TOTAL : 50

Now you want to get sum of total Fee per candidate : 25

Total rows: 2

Distinct rows :1

Sum of all “Fee” column : 25+25= 50

Formula : (50*1)/(2)

Query will be :

SELECT sum(course.fee)*count(DISTINCT course.student_id)/count(*) as TOTAL
FROM `student_details` AS student LEFT JOIN course_details AS course ON course.student_id = student.id
LEFT JOIN college_details AS college ON college.student_id = student.id WHERE student.id=1;

you will get result:

TOTAL : 25