Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME =
> SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION =
> SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE =
> SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>