CODEWITHSUNDEEP
mysqlsql
sqrepants
sqrepants

Reputation: 1106

Complex SQL Select query with inner join

My SQL query needs to return a list of values alongside the date, but with my limited knowledge I have only been able to get this far.

This is my SQL:

select lsu_students.student_grouping,lsu_attendance.class_date,
count(lsu_attendance.attendance_status) AS count
from lsu_attendance 
inner join lsu_students
ON lsu_students.student_grouping="Central1A" 
and lsu_students.student_id=lsu_attendance.student_id
where lsu_attendance.attendance_status="Present"
and lsu_attendance.class_date="2015-02-09";

This returns:

student_grouping class_date count
Central1A        2015-02-09 23

I want it to return:

student_grouping class_date     count
Central1A         2015-02-09    23
Central1A         2015-02-10    11
Central1A         2015-02-11    21
Central1A         2015-02-12    25

This query gets the list of the dates according to the student grouping:

select distinct(class_date)from lsu_attendance,lsu_students
where lsu_students.student_grouping like "Central1A" 
and lsu_students.student_id = lsu_attendance.student_id
order by class_date

Upvotes: 0

Views: 43

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you just want a group by:

select s.student_grouping, a.class_date, count(a.attendance_status) AS count
from lsu_attendance a inner join
     lsu_students s
     ON s.student_grouping = 'Central1A' and
        s.student_id = a.student_id
where a.attendance_status = 'Present'
group by s.student_grouping, a.class_date;

Comments:

  • Using single quotes for string constants, unless you have a good reason.
  • If you want a range of class dates, then use a where with appropriate filtering logic.
  • Notice the table aliases. The query is easier to write and to read.
  • I added student grouping to the group by. This would be required by any SQL engine other than MySQL.

Upvotes: 2

Brad R
Brad R

Reputation: 61

The group by clause is what you're looking for, to limit aggregates (e.g. the count function) to work within each group.

To get the number of students present in each group on each date, you would do something like this:

select student_grouping, class_date, count(*) as present_count
from lsu_students join lsu_attendance using (student_id)
where attendance_status = 'Present'
group by student_grouping, class_date

Note: for your example, using is simpler than on (if your SQL supports it), and putting the table name before each field name isn't necessary if the column name doesn't appear in more than one table (though it doesn't hurt).

If you want to limit which data rows get included, put your constraints get in the where clause (this constrains which rows are counted). If you want to constrain the aggregate values that are displayed, you have to use the having clause. For example, to see the count of Central1A students present each day, but only display those dates where more than 20 students showed up:

select student_grouping, class_date, count(*) as present_count
from lsu_students join lsu_attendance using (student_id)
where attendance_status = 'Present' and student_grouping = 'Central1A'
group by student_grouping, class_date
having count(*) > 20

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

Just take out and lsu_attendance.class_date="2015-02-09" or change it to a range, and then add (at the end) GROUP BY lsu_students.student_grouping,lsu_attendance.class_date.

Upvotes: 1

Related Questions