Evenly distributing aggregates.

  • Thread starter Thread starter GPO
  • Start date Start date
G

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
 
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;
 
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
Click to expand...

.
Click to expand...
 
Back
Top