Evenly distributing aggregates.

[GPO]

Hi All,

Say you have the following table (tblTest) of data:

ID Score
1 3
2 4
3 3
4 6
5 3
6 2
7 2
8 2
9 2
10 1
11 0

Now run the query below:

SELECT Score, Count(*) AS [count]
FROM tblTest
GROUP BY Score;

You should get the following results (note no score of 5):

Score count
0 1
1 1
2 4
3 3
4 1
6 1

How do you change things so that the results look like the
following:

Score count
0 1
1 1
2 4
3 3
4 1
5 0
6 1

I tried an outer join from a reference table
(tblRefScore). tblRefScore is simply:

Reference
1
2
3
4
5
6
7

The sql:

SELECT tblTest.Score, Count(*) AS [count]
FROM tblRefScore LEFT JOIN tblTest ON
tblRefScore.Reference = tblTest.Score
GROUP BY tblTest.Score, tblRefScore.Reference
ORDER BY tblRefScore.Reference;

This gives me:

Score count
0 1
1 1
2 4
3 3
4 1
1
6 1
1

Which is not what I want. Any ideas?

Regards

GPO

 

[Sandra Daigle]

You must do the summary query on tblTest first. Then perform the Left Join
with the reference table. You can do this in two queries or as a nested
query:

SELECT
tblRefScore.Reference,
nz(qrySummaryScores.ScoreCount,0) AS CountThisScore
FROM
tblRefScore
Left Join
(Select
tblTest.Score,
Count(tblTest.Score) as ScoreCount
From
tblTest
Group by
tblTest.Score) AS qrysummaryScores
on qrysummaryscores.score=tblRefscore.reference
ORDER BY
tblRefScore.reference;

If you prefer "stacked" queries, then the summary query would be saved as
qrySummaryScores and the SQL for it is:

Select
tblTest.Score,
Count(tblTest.Score) as ScoreCount
From
tblTest
Group by
tblTest.Score

Then SQL for the outer query would be:

SELECT
tblRefScore.Reference,
nz(qrySummaryScores.ScoreCount,0) AS CountThisScore
FROM
tblRefScore Left Join qrySummaryScores
on qrysummaryscores.score=tblRefscore.reference
ORDER BY
tblRefScore.reference;

 

[GPO]

Much obliged.

It was the
nz(qrySummaryScores.ScoreCount,0)
that was killing me.

GPO

-----Original Message-----
You must do the summary query on tblTest first. Then perform the Left Join
with the reference table. You can do this in two queries or as a nested
query:

SELECT
tblRefScore.Reference,
nz(qrySummaryScores.ScoreCount,0) AS CountThisScore
FROM
tblRefScore
Left Join
(Select
tblTest.Score,
Count(tblTest.Score) as ScoreCount
From
tblTest
Group by
tblTest.Score) AS qrysummaryScores
on qrysummaryscores.score=tblRefscore.reference
ORDER BY
tblRefScore.reference;

If you prefer "stacked" queries, then the summary query would be saved as
qrySummaryScores and the SQL for it is:

Select
tblTest.Score,
Count(tblTest.Score) as ScoreCount
From
tblTest
Group by
tblTest.Score

Then SQL for the outer query would be:

SELECT
tblRefScore.Reference,
nz(qrySummaryScores.ScoreCount,0) AS CountThisScore
FROM
tblRefScore Left Join qrySummaryScores
on qrysummaryscores.score=tblRefscore.reference
ORDER BY
tblRefScore.reference;



--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi All,

Say you have the following table (tblTest) of data:

ID Score
1 3
2 4
3 3
4 6
5 3
6 2
7 2
8 2
9 2
10 1
11 0

Now run the query below:

SELECT Score, Count(*) AS [count]
FROM tblTest
GROUP BY Score;

You should get the following results (note no score of 5):

Score count
0 1
1 1
2 4
3 3
4 1
6 1

How do you change things so that the results look like the
following:

Score count
0 1
1 1
2 4
3 3
4 1
5 0
6 1

I tried an outer join from a reference table
(tblRefScore). tblRefScore is simply:

Reference
1
2
3
4
5
6
7

The sql:

SELECT tblTest.Score, Count(*) AS [count]
FROM tblRefScore LEFT JOIN tblTest ON
tblRefScore.Reference = tblTest.Score
GROUP BY tblTest.Score, tblRefScore.Reference
ORDER BY tblRefScore.Reference;

This gives me:

Score count
0 1
1 1
2 4
3 3
4 1
1
6 1
1

Which is not what I want. Any ideas?

Regards

GPO

.