CODEWITHSUNDEEP
mysqlsql
user6201447
user6201447

Reputation:

How to return multiple rows when using Aggregate functions?

SELECT StudentID, Fname, LName, S_LessonNumber, LessonName, Date, Cost
FROM STUDENT_2
JOIN LESSON ON S_LessonNumber = LessonNumber
NATURAL JOIN STUDENT_1
WHERE StudentID = '1001'

The resulting table I get with this query is as follows,

enter image description here

When attempting to display the total amount paid, and the total number of lessons taken, using the following query, I was only able return one row.

SELECT StudentID, Fname, LName, S_LessonNumber, LessonName, Date,
Cost,COUNT( DISTINCT S_LessonNumber ) , SUM( Cost )
FROM STUDENT_2
JOIN LESSON ON S_LessonNumber = LessonNumber
NATURAL JOIN STUDENT_1
WHERE StudentID = '1001'

enter image description here

Is there a way that I can return all 4 rows with the values for COUNT(DISTINCT S_LessonNumber) and SUM(Cost) repeated.

The desired output is as follows:

StudentID FName    LName   S_LessonNumber LessonName Date Cost COUNT SUM
1001      Hannibal Lecter  7              C---       ---  15   4     60 
1001      Hannibal Lecter  6              Wa--       ---  15   4     60 
1001      Hannibal Lecter  5              Tri--      ---  15   4     60 
1001      Hannibal Lecter  1              Cha-       ---  15   4     60

Upvotes: 2

Views: 1709

Answers (2)

McNets
McNets

Reputation: 10827

Check it here: http://rextester.com/SBUQ82088

create table if not exists fstudents (id int, fname text, lname text);
create table if not exists fstudents2 (student_id int, lesson_number int, lesson_date date, cost int);
create table if not exists flessons (number int, name text);
insert into fstudents values (1001, 'Anibal', 'Lecter');
insert into flessons values (1, 'Cha Cha');
insert into flessons values (2, 'Waltz');
insert into flessons values (3, 'Country');
insert into flessons values (4, 'Triple2');
insert into fstudents2 values (1001, 1, '2016-10-06', 15);
insert into fstudents2 values (1001, 2, '2016-10-07', 15);
insert into fstudents2 values (1001, 3, '2016-10-08', 15);
insert into fstudents2 values (1001, 4, '2016-10-09', 15);

Use a subquery to count an sum, and then join it to main query.

select st2.student_id, st.fname, st.lname, ls.name, st2.lesson_date, st2.cost, agg.courses, agg.total_cost
from fstudents2 st2
    join fstudents st on st2.student_id = st.id
    join flessons ls on st2.lesson_number = ls.number
    join (select st3.student_id, count(st3.lesson_number) courses, sum(st3.cost) total_cost 
          from fstudents2 st3
          group by st3.student_id) agg on agg.student_id = st2.student_id
where
    st2.student_id = 1001;

Upvotes: 0

golimar
golimar

Reputation: 2548

Aggregate functions will always return 1 row. If using subqueries is not a problem, you can do:

SELECT StudentID, Fname, LName, S_LessonNumber, LessonName, Date, Cost,
  (SELECT COUNT( DISTINCT S_LessonNumber ) FROM STUDENT_2 JOIN LESSON ON S_LessonNumber = LessonNumber NATURAL JOIN STUDENT_1 WHERE StudentID = '1001') AS COUNT,
  (SELECT SUM( Cost ) FROM STUDENT_2 JOIN LESSON ON S_LessonNumber = LessonNumber NATURAL JOIN STUDENT_1 WHERE StudentID = '1001') AS SUM
FROM STUDENT_2
JOIN LESSON ON S_LessonNumber = LessonNumber
NATURAL JOIN STUDENT_1
WHERE StudentID = '1001'

Upvotes: 1

Related Questions