Complex SQL Select query with inner join

Question

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

Hello d1sciple welcome to StackOverflow, next time try to provide a SqlFiddle so we can understand the problem better and give you an answer much faster – Also please read How to ask And How to create a Minimal, Complete, and Verifiable example. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 26, 2015 at 18:55

Gordon Linoff · Accepted Answer · 2015-08-27 11:23:21Z

2

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.

edited Aug 27, 2015 at 11:23

answered Aug 26, 2015 at 18:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Uueerdo Over a year ago

Looks almost how I write them, but I put [inner|left] join at the start of the lines to make it more obvious how the additional tables are being used, and the same with and.... btw, one too many ands in the ON

Uueerdo · Accepted Answer · 2015-08-26 18:58:01Z

1

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.

answered Aug 26, 2015 at 18:58

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Comments

Brad R · Accepted Answer · 2015-08-26 19:19:23Z

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

Collectives™ on Stack Overflow

Complex SQL Select query with inner join

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related