I have gotten even closer to my end result by adapting the SQL expression to
this:

SELECT X1.LAB_ID, 'max_of' AS aggregate_type,
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'sum_of',
SUM(IIF(X1.QUAL IS NOT NULL, NOT NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NOT NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

by reversing the order of the "SUM" and "MAX" functions, as well as the
"WHERE/WHERE NOT EXISTS", to yield these results:

LAB_ID aggregate_type aggregate_value
711721 sum_of 2
711722 sum_of
711722 max_of 0.4
711723 sum_of 4700

from this set of test data (added a couple more rows to original dataset):

SAMPLE DATE LAB_ID ANALYTE RESULT QUAL
RW-6 2/24/2006 711721 o-Xylene 0.4 ND
RW-6 2/24/2006 711721 o-Xylene 2
RW-5 2/24/2006 711722 o-Xylene 0.4 ND
RW-1 2/24/2006 711723 m+p-Xylene 2800
RW-1 2/24/2006 711723 o-Xylene 1900
RW-1 2/24/2006 711723 m+p-Xylene 1 ND
Duplicate 2/24/2006 711724 m+p-Xylene 2500
Duplicate 2/24/2006 711724 o-Xylene 1700

So, the SQL is accurately selecting and sums the results with null [Qual]'s
over those with not null [Qual]'s (very good).
The only issue that remains is that it did not include a SUM of the two rows
of data with the [Lab_id] = "711724"; it appears for some reason that it was
due to both rows of data having null [Qual]'s. Maybe because it is only
comparing null/not null [Qual]'s and not additionally only summing the
results of data with all null [Qual]'s in the same group [Lab_id], right??
--
Barry Guidry


"Jamie Collins" wrote:


Jamie Collins wrote:

The proposed solution snipped

After peeking at John Spencer's proposal:

SELECT X1.LAB_ID, 'sum_of' AS aggregate_type,
SUM(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT])) AS aggregate_value
FROM Xylenes AS X1
WHERE EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
UNION ALL
SELECT X1.LAB_ID, 'max_of',
MAX(IIF(X1.QUAL IS NULL, NULL, X1.[RESULT]))
FROM Xylenes AS X1
WHERE NOT EXISTS (
SELECT *
FROM Xylenes AS X2
WHERE X2.LAB_ID = X1.LAB_ID
AND X2.QUAL IS NULL
)
GROUP BY X1.LAB_ID
ORDER BY 1;

Jamie.

--