Access SUMs Full Column When Using Subquery
From: Chris2 (rainofsteel.NOTVALID_at_GETRIDOF.luminousrain.com)
Date: 02/27/05
- Next message: Douglas J. Steele: "Re: Retrive informations fro two lists"
- Previous message: RNUSZ_at_OKDPS: "Missing Data in Query Results"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 27 Feb 2005 12:12:45 -0800
I'm running Win2k SP-4, MS Access 2000 SP-3, and JET 4.0 SP-8.
While working on a question posted earlier, Access gave me results I
couldn't figure out.
I jumped over to SQL Server, and it gave me the results I wanted, so
it must be based in something about JET that I don't understand.
The problem is that when I use a subquery against a table that does
SELECT SUM(column) FROM MyTable, where the match retrieves but one
record, Access still runs the SUM against the whole column in MyTable.
Here's the DDL, Data, and SQL
--------------------------
CREATE TABLE Students_02272005_1
(Stud_ID LONG
,CONSTRAINT pk_Students_02272005_1 PRIMARY KEY (Stud_ID)
)
Sample Data: Students_02272005_1
1
2
3
CREATE TABLE Lessons_02272005_1
(Lesson TEXT(10)
,CONSTRAINT pk_Lessons PRIMARY KEY (Lesson)
)
Sample Data: Lesson_02272005_1
Math
English
Biology
CREATE TABLE Exams_02272005_1
(ExamID AUTOINCREMENT
,Stud_ID LONG
,Lesson TEXT(10)
,Exam DOUBLE
,CONSTRAINT pk_Exams_02272005_1 PRIMARY KEY (ExamID)
,CONSTRAINT fk_Exams_Students_02272005_1
FOREIGN KEY (Stud_ID)
REFERENCES Students_02272005_1 (Stud_ID)
,CONSTRAINT fk_Exams_Lessons_02272005_1
FOREIGN KEY (Lesson)
REFERENCES Lessons_02272005_1 (Lesson)
)
Sample Data: Exams_02272005_1
ExamID Stud_ID Lesson Exam
1 1 Math 4
2 1 Math 6
3 1 English 3
4 1 English 4
5 1 Biology 6
6 1 Biology 6
7 1 Biology 6
8 2 Math 6
9 2 Math 6
10 2 English 2
11 2 English 2
12 2 Biology 4
13 2 Biology 4
14 2 Biology 6
15 3 Math 1
16 3 Math 1
17 3 English 6
18 3 English 6
19 3 Biology 4
20 3 Biology 6
Query: Exams_02272005_1_Query_1
SELECT E1.Stud_ID
,E1.Lesson
,COUNT(*) AS LessonCount
FROM Exams_02272005_1 AS E1
GROUP BY E1.Stud_ID
,E1.Lesson
Query: Exams_02272005_1_Query_2
SELECT E1.Stud_ID
,E1.Lesson
,(COUNT(*) /
(SELECT SUM(E01.LessonCount)
FROM Exams_02272005_1_Query_1 AS E01
WHERE E01.Stud_ID = E1.Stud_ID
AND E01.Lesson = E1.Lesson)) AS PerecentAbove5
FROM Exams_02272005_1 AS E1
WHERE E1.Exam > 5
GROUP BY E1.Stud_ID
,E1.Lesson
Query_2 above should only be pulling one row from the subquery on the
SELECT clause due to conditions specified on the subquery's WHERE
clause.
When I execute it, though, it returns the value 20 for every single
row.
Stud_ID Lesson PercentAbove5
1 Biology 20
1 Math 20
2 Biology 20
2 Math 20
3 Biology 20
3 English 20
20 is the SUM of the full LessonCount column from the Query:
Exams_02272005_1_Query_1. Given the conditions on the subquery's
WHERE clause, how is this happening?
The above query, in SQL Server, operating on the same data, produces
the results I'd expect (with some CAST statements tossed in to force
use of DECIMAL(10,2)):
Stud_ID Lesson PercentAbove5
----------- ---------- ---------------------------------------
1 Biology 1.000000000000000
1 Math .500000000000000
2 Biology .333333333333300
2 Math 1.000000000000000
3 Biology .500000000000000
3 English 1.000000000000000
Command(s) completed successfully.
If anyone has an explanation of why JET is doing this, I'd appreciate
it.
Sincerely,
Chris O.
- Next message: Douglas J. Steele: "Re: Retrive informations fro two lists"
- Previous message: RNUSZ_at_OKDPS: "Missing Data in Query Results"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
