COUNT problem (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

DesiMcK -> COUNT problem (5/6/2008 15:59:18)

Hi all,

this is my current SQL which works fine:

SELECT tblPupil.TutorGroup, Count(tblBehaviour.ID) AS total FROM tblPupil INNER JOIN tblBehaviour ON tblPupil.PupilID = tblBehaviour.PupilID WHERE ([Level] = 'Ac' AND tblPupil.TutorGroup LIKE '7%') GROUP BY tblPupil.TutorGroup ORDER BY TutorGroup ASC

this is then used to produce a table with two columns: Tutor Group | Number of Aces

However I want to add a new column - number of aces this week. How can I achieve this. If I add the date constraint into the WHERE clause I can't get the total number.

Thanks,

Desi



rdouglass -> RE: COUNT problem (5/6/2008 16:27:03)

How about trying 'datepart'? If you're constraining by week:

http://doc.ddart.net/mssql/sql70/da-db_8.htm

Just a quick guess but I'd be trying something like this:

SELECT tblPupil.TutorGroup, Count(tblBehaviour.ID) AS total,DatePart(ww,dateFieldName) As WeekNumber FROM tblPupil INNER JOIN tblBehaviour ON tblPupil.PupilID = tblBehaviour.PupilID WHERE ([Level] = 'Ac' AND tblPupil.TutorGroup LIKE '7%') GROUP BY tblPupil.TutorGroup,DatePart(ww,dateFieldName) ORDER BY TutorGroup ASC

Then a "WHERE" or a "HAVING" in there somewhere to pick the week you want. Haven't checked any of it but that would be where I'd go.

Does that help any or am I reading the Q wrong?



DesiMcK -> RE: COUNT problem (5/6/2008 17:44:02)

This is what I want in the end:

Class 1 - 15 aces in the past 7 days - 103 overall this year

If I add DateField > Date() - 7 to the WHERE clause, I get the count for the week, but I can't show the otherall count.

Am I missing something obvious?

Desi



rdouglass -> RE: COUNT problem (5/7/2008 9:01:46)

So it looks like you want a grand total and then a total for a week?

By it's very nature you have 2 different criteria there (but you knew that already.) You could probably use Derrived tables and a stored procedure or two but wouldn't it be easier to use 2 queries?

Or is this a report that you'd have a new query for each week of the report?



DesiMcK -> RE: COUNT problem (5/7/2008 14:36:09)

I can change my original idea if it's easier to have a two way table.

Basically if I could achieve a table such as this then I would be very happy.

|Wk1 | Wk2 | Wk 3 | etc... | Total
Pupil 1 |
Pupil 2 |
Pupil 3 |

Does his make more sense?

Thanks for looking at this,

Desi



DesiMcK -> RE: COUNT problem (5/12/2008 10:05:37)

I have now managed to do what I
want - not elegent but it works.

select pupilid,
sum(iif (datepart('ww',incidentdate) = 1,1,0)) as wk1,
sum(iif (datepart('ww',incidentdate) = 2,1,0)) as wk2,
sum(iif (datepart('ww',incidentdate) = 3,1,0)) as wk3,
sum(iif (datepart('ww',incidentdate) = 4,1,0)) as wk4,
...etc...
Count(PupilID) as myTotal FROM tblBehaviour WHERE [Level] = 'Ac'
GROUP BY pupilid

Then I looped through the months in a table:

<%for n = 1 to 52%>
<td align="center"><%=FP_FieldVal(fp_rs,"wk" & n)%></td>
<%next%>

Thanks for your help.

Desi



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.171875