Understanding Group By

[shauns1]

Hi

Before I start, this is the structure of my table, tblStatsSessions.

SessionID (primary key, auto number)
CreationDate (DateTime)
DevelopmentID (Number)


The purpose of the function below(a stored procedure in Access) is to return a Count of SessionID's for the past 7 days and group them by day of the week for a specific DevelopmentID.

For example:
I feed it a DevelopmentID of 1. It then displays the number of sessions for that DevelopmentID for each day within the last 7 days. I use DatePart to display the number of the day of the week rather than the date.

Here is the SQL block:

SELECT DevelopmentID, Count(SessionID) AS Sessions, DatePart ( 'w', CreationDate) as StrDay
FROM tblStatsSessions
WHERE DevelopmentID = @DevelopmentID AND DateDiff('d',CreationDate, Now())<7
GROUP BY DatePart ( 'w', CreationDate), DevelopmentID
ORDER BY CreationDate DESC;

The SQL above works fine until I want to Order By the field 'CreationDate'. To do that I believe I must include CreationDate into the SELECT statement and also include it in the Group By line. However, when I do this, rather than displaying a Count of Session ID's for each day of the week, I get a record for every SessionID (so I presume, every CreationDate).

How do I ensure that I can Order By CreationDate, but not include it in the Select/Group By lines?

Thanks in advance for any help on this.

Shaun

 

[Golom]

The fundamental problem here is that each grouped record may include data from multiple different "CreationDate" records. SQL can't decide how to sort by that field because it doesn't know which creation date to attach to the grouped record for sorting purposes.
You may try something like

SELECT DevelopmentID, 
       Count(SessionID) AS Sessions, 
       (Select MIN(CreationDate) 
        From tblStatsSessions As X
        Where X.DevelopmentID = S.DevelopmentID) As CrDate,
       DatePart ( 'w', CreationDate) as StrDay

FROM tblStatsSessions As S

WHERE     DevelopmentID = @DevelopmentID 
      AND DateDiff('d',CreationDate, Now())<7

GROUP BY DatePart ( 'w', CreationDate), DevelopmentID

ORDER BY 3 DESC;

 

[shauns1]

Thanks. It didn't quite work as there are multiple instances of DevelopmentID. I forgot to mention this so you weren't to know.

So I played with your code and actually, it seems to work simply like this:

SELECT DevelopmentID, Count(SessionID) AS Sessions, MIN(CreationDate) As CrDate, DatePart ( 'w', CreationDate) as StrDay

FROM tblStatsSessions As S

WHERE DevelopmentID = @DevelopmentID
AND DateDiff('d',CreationDate, Now())<7

GROUP BY DatePart ( 'w', CreationDate), DevelopmentID

ORDER BY 3 DESC;

Beautiful!

Thanks for your help.

Shaun