Count Group Members

Hi,

I'm trying to make a matrix report for current and previous month. I've gotten the matrix to show current month dat on the top data region and previous month on the side data region. Now i'm trying to compare and count group members for current and previous month.

example: count for students with the same exam score. If a student got 20 on thier exam Previous month and 20 on their current month exam, they will be counted as 1.

20

40

60

80

100

20

2

4

0

7

1

60

0

1

0

4

0

80

2

0

3

0

0

so the data shows 2 students who got 20 last month also got 20 this month. It also shows 4 students who got 20 last month managed to get 40 this month, and so on.

How do i count the students as i'm using student ID.

Thanks in advance.

Hi,

count is overloaded. YOu can also use that with a group expressions COUNT(SomeValue, SomeGroup)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Thanks for the reply. I've already tried that method. the count only produced 0, but there is data to be counted.

the data grouping is like this: Year -> Month -> Class -> ID -> Score becuase the score is the measure or detail data. However i group the data in the report by Year -> Month -> Class -> Score and then count the IDs that are associated with each distinct score value.

This is to compare how many student got a certain score amount and what are thier scores for the current month as in the sample I have given previous post.

Hope this is much clearer explanation than before.

|||Here's an idea for getting this to work. When querying for the data, join the table with itself, based on the student's id and the month. This will give you one dataset with the student's data for each month compared to each of the following months that you can use to build the matrix.
Using this data

TestDate ID Score
1/1/2000 1 20
1/1/2000 2 20
1/1/2000 3 20

1/1/2000 4 20
1/1/2000 5 20

1/1/2000 6 20
2/1/2000 1 20

2/1/2000 2 20

2/1/2000 3 40

2/1/2000 4 40

2/1/2000 5 40

2/1/2000 6 40

The query would look something like this

Select t1.TestDate AS TestDate, t2.TestDate AS TestDate2, t1.Score AS Score, t2.Score AS Score2, t1.ID FROM TestScores As t1 INNER JOIN TestScores As t2 ON t1.ID = t2.ID AND t1.TestDate < t2.TestDatesql