Search using a column not in the select statement because of group by

[jkrainak]

So basically I have a students table and a discipline table. Each row of the discipline table is a student id, date and columns we call "jugs" and "demerits". I'm trying to build a view that would allow a user to search by date any students that have a matching record in the discipline table for that date and give me the total demerits and jugs for ALL the dates for that student (GROUP BY student id). I cannot however do this because adding the date to the select statement breaks the group by. Is there a way to add the date search into my asp.net maker app by declaring variables? Something like this.

DECLARE @StartDate DATE
DECLARE @EndDate DATE

SELECT
    s.ID AS StudentID,
    s.FIRST_NAME,
    s.LAST_NAME,
    SUM(d.Demerits) AS TotalDemerits,
    SUM(d.Jugs) AS TotalJugs
FROM
    [dbo].[viewStudents] s
JOIN
    (
        SELECT *
        FROM [dbo].[Discipline]
        WHERE givenDate >= @StartDate
          AND givenDate <= @EndDate
    ) d ON s.ID = d.Student
GROUP BY
    s.ID, s.FIRST_NAME, s.LAST_NAME
ORDER BY
    s.LAST_NAME, s.FIRST_NAME;

[MichaelG]

Return the max and min date in the SQL so you can search between the dates. Something like:

SELECT
s.ID AS StudentID,
s.FIRST_NAME,
s.LAST_NAME,
SUM(d.Demerits) AS TotalDemerits,
SUM(d.Jugs) AS TotalJugs,
MAX(d.givenDate) AS MaxDate,
MIN(d.givenDate) AS MinDate
FROM
[dbo].[viewStudents] s
JOIN [dbo].[Discipline] d
ON s.ID = d.Student
GROUP BY
s.ID, s.FIRST_NAME, s.LAST_NAME
ORDER BY
s.LAST_NAME, s.FIRST_NAME;

[jkrainak]

I believe this will work, but what I want is the total records for all dates. In other words, if a student record exists for whatever date was searched, give me the students total records across all dates. Our registrar basically says "ok who got a demerit today, oh Billy did. Ok, how many total demerits does Billy have?"

[MichaelG]

The SQL merely returns the Max and Min date for your searching. It still returns the total records for all dates.