Subscribe to this thread
Home - General / All posts - Aggregates and DISTINCT
dyalsjas
157 post(s)
#14-Feb-18 02:35

Continuing to work through Fehily 2016 using books.map. I completed the topic for aggregating distinct values but I'm not sure if I followed the exercise closely enough.

The aggregating distinct values text listing from Fehily was:

SELECT

    COUNT(DISTINCT price)

      AS "COUNT(DISTINCT)",

    SUM(DISTINCT price)

      AS "SUM(DISTINCT)",

    AVG(DISTINCT price)

      AS "AVG(DISTINCT)"

  FROM titles;

I was able to modify a separate text listing for MS Access that used a sub-query as a column expression.

SELECT

    COUNT([price]AS [COUNT DISTINCT],

      SUM([price]AS [SUM DISTINCT],

      AVG([price]AS [AVG DISTINCT]

  FROM (SELECT DISTINCT [price]

          FROM [titles]);

Is this the most efficient query? If I read the user manual correctly, Manifold doesn't support modifying all aggregation functions with DISTINCT. Use cases for aggregating distinct values seem tenuous to me, but there may be implications when using big-data tables that I don't recognize.

The SQL statement compositions for Manifold 9 seem to follow MS Access conventions in unexpected places.

Thoughts?

tjhb
10,094 post(s)
#14-Feb-18 02:49

See Adam's explanation for departure from standard here (and surrounding posts).

And yes, it is the most efficient query.

dyalsjas
157 post(s)
#14-Feb-18 03:03

Thank you. That helps (I think) Hmmm,

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.