"windandwaves" <wi*********@coldmail.com> wrote in message
news:G5**********************@news.xtra.co.nz...
I like to have good habits and a while ago I decided to stick with one way
of writing all my queries.
I'd suggest the following additional habits:
1. Use the delimited identifiers consistently if you use them at all.
2. Use table aliases.
3. List the table alias prefix for all fields in all clauses of the query,
to make it more clear in which table each column is found.
4. Use the JOIN syntax instead of SQL-89 join style. If you do, your goal
of one way of writing queries will be easier to achieve when you start using
outer joins.
5. Avoid naming columns "ID". It's more clear to use SIS_ID and SID_ID for
both referenced and referencer of a given column.
6. You might want to read Joe Celko's book "SQL Programming Style" if good
habits and consistent style are important to you.
It's hard to analyze your query to suggest another syntax, because I can't
tell for certain which table D and BRO belong to. I can infer that SID
includes columns ID and D, while SIS probably includes columns ID, SID_ID,
and BRO. Please post a clear description of your schema when you post
questions to newsgroups. The best description is a dump of the CREATE TABLE
statements to create the schema, including referential integrity
constraints.
Your desired query computes the average per D count of SIS's per ID.
(So in other words, if SID is an order, and SIS is a line-item in an order,
and D is a department submitting orders within the organization, you want
the average number of line-items in an order, per department.)
The groupings applied by GROUP BY apply to all aggregate functions in that
query. So you need to do the two calculations in separate queries. Either
store the count per SIS.ID in a temp table, or else feed it to the average
calculation by use of a subquery in a derived view:
SELECT AVG(R.`C`) AS Avg, R.`D`
FROM (
SELECT COUNT( S.`ID` ) AS C, D.`D`
FROM `SIS` AS S INNER JOIN `SID` AS D ON S.`SID_ID` = D.`ID`
WHERE S.`BRO` <> 'bot'
GROUP BY D.`ID`
) AS R
GROUP BY R.`D`
Regards,
Bill K.