Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    475

    Expression Doesn't Include Aggregate Function

    I get the error . . . You tried to execute a query that does not include the specified expression "SellName" as part of an aggregate function.
    I can't figure it out in this example.

    The third set of Sum(iif . . . produces incorect results.

    What am I missing here?

    Thanks, Rick . . .

    SELECT Scmls.ListName, Scmls.SellName, Scmls.SalePrice,

    Sum(IIf(Scmls.ListName And Scmls.SellName Like Scmls.ListName,1,0))*2 AS WSO,
    Sum(IIf(Scmls.ListName And Scmls.SellName Like Scmls.ListName,Scmls.SalePrice,0))*2 AS WSODV,

    Sum(iif(Scmls.ListName <> Scmls.SellName,1,0)) AS TSO,
    Sum(iif(Scmls.ListName <> Scmls.SellName,Scmls.SalePrice,0)) AS TSODV,

    Sum(iif(Scmls.SellName <> Scmls.ListName,1,0)) AS WST,
    Sum(iif(Scmls.SellName <> Scmls.ListName,Scmls.SalePrice,0)) AS WSTDV,

    WSO+TSO+WST AS [Total Transactions],
    Sum(IIf(Scmls.ListName,Scmls.SalePrice,0)) AS [Listing Dollar Volume],
    WSODV+TSODV+WSTDV AS [Listing and Sales Dollar Volume]

    FROM Scmls
    GROUP BY Scmls.ListName
    ORDER BY 10 DESC;

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Your Group By statement should include all the fields that are not SUMMED in your SELECT statement. Right now you have 3 in the select, but only one in the group by.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    475

    Corrected but created another failure

    Thanks RedNeckGeek

    I made that correction but now the second and third set of
    Sum(iif( . . . statements fail to calculate at all.

    I'm not understanding the relationships between the GROUP BY, ORDER BY and HAVING scenarios, and how they effect the Sum(iif( . . . statements.

    What show I be looking for here?

    Thanks again . . . Rick

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    In the first "set", what is this supposed to be evaluating?

    Scmls.ListName And Scmls.SellName Like Scmls.ListName

    That looks strange, but it may just be something I've never tried before...
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •