Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Count & Sum Totals vs Individually

    Code:
    SELECT Scmls.ListName, Count([Scmls.ListName]) AS TSO, Sum([Scmls.SalePrice]) AS TSODV
    FROM Scmls
    GROUP BY Scmls.ListName;
    This works great!

    However, if I add this . . .

    Code:
     HAVING (((Scmls.SellName)<>[ListName]));
    . . . each occourence of SELECT Scmls.ListName shows up in the dynaset as 1 count and 1 dollar amount in lieu of totalling the counts and dollar amounts for occourence of ListName.

    Incorrect . . .

    FIRST TEAM MV 1 200,000
    FIRST TEAM MV 1 345,000
    FIRST TEAM MV 1 178,000
    FIRST TEAM MV 1 212,000
    FIRST TEAM MV 1 200,000

    Correct . . . . . . 5 1,135,000

    I need to have the HAVING clause in there.

    I’m pulling my hair out on this.

    Thanks . . . Rick

  2. #2
    Join Date
    Oct 2003
    Posts
    21
    Why is that a "having" and not a "where"?

    SELECT Scmls.ListName, Count([Scmls.ListName]) AS TSO, Sum([Scmls.SalePrice]) AS TSODV
    FROM Scmls
    WHERE (((Scmls.SellName)<>[ListName]))
    GROUP BY Scmls.ListName;

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Error in using WHERE clause. . .

    Syntax error, missing operator in Scmls.SellName
    WHERE ((([Scmls.SellName])<>[Scmls.ListName]));

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Uh . . . you were right in your posting. I missed the rearrangement of the FROM WHERE and GROUP.

    Thanks much Gussery!

    Rick

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Rick Schreiber
    Error in using WHERE clause. . .

    Syntax error, missing operator in Scmls.SellName
    WHERE ((([Scmls.SellName])<>[Scmls.ListName]));
    Get rid of the square brackets - they indicate a single field not a qualified field name.
    Code:
     
    WHERE Scmls.SellName<>Scmls.ListName
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - your post beat me to it
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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