Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Question Unanswered: Got a strange error message when trying to do counters in a query.

    Hi,

    I'm trying to write a query for a report and one of the attributes of this report requires to have a counter of how many records there are per category. This is the SQL i got so far:

    SELECT KE0G_Drawings.DwgTag, KE0G_Drawings.DwgSht, KE0G_Drawings.DwgTitle1, KE0G_Drawings.DwgRevInt, KE0G_Drawings.DwgIssue, KE0G_Drawings.DwgType, KE0G_Drawings.DwgCommnt,
    DateDiff("y",[KE0G_Drawings].[dwgIssue],Now()) AS DaysDifference, KE00_Disciplines.Description, KE0G_Drawings.DwgDiscipline,
    IIf([DaysDifference]<14,"Less Than 2 Weeks","More Than 2 Weeks") AS age,
    Sum(IIf(([DaysDifference] Is Not Null) OR ([DaysDifference]<14),1,0)) AS ageCounter
    FROM KE00_Disciplines
    INNER JOIN KE0G_Drawings ON KE00_Disciplines.DisciplineCode = KE0G_Drawings.DwgDiscipline
    WHERE (((KE0G_Drawings.DwgRevInt) Like "p%") AND ((KE0G_Drawings.DwgIssue) Is Not Null))
    ORDER BY DateDiff("y",[KE0G_Drawings].[dwgIssue],Now()) DESC;


    I resently put in the Sum counter I called ageCounter. This would be responsible for counting one of the categories. There are only 2 categories in this report; More than and Less Than.

    Without that Sum counter the query works fine. As soon as I put it in I get this:

    "You tried to execute a query that does not include the specified expression 'DwgTag' as part of an aggregate function"

    This looks like a logic problem and I have my suspicions it is because I try to count from a 'counter' field, not from the table. But I see no other way of counting those records than by filtering the days.

    The end result will (or should) be a field that counts all records less than 14 days and prints the sum. Then another that counts more than or equal to 14 days. Then another expression that combines those 2 fields into one and displays it per category.

    So if anyone knows how to do this better, or knows how to go around that error, please help.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you use SUM, you need to use GROUP BY (unless the SUM is the only column in the SELECT)

    i can't understand what the rest of your query is doing

    for example, DaysDifference is an alias for the number of years between [KE0G_Drawings].[dwgIssue] and today

    DaysDifference = years?


    also, inside the SUM, there is no need to test DaysDifference for null, for two reasons -- SUM ignores nulls, and the WHERE clause already filters them out anyway!

    anyhow, for your GROUP BY, you need to put all the columns in the SELECT list except the SUM into the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    DaysDifference calculates the number of days between 2 dates. Not sure what the "y" stands for, I took it off access help.
    Last edited by YevSnow; 04-13-04 at 07:58.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, my bad, "yyyy" gets the difference in years, "y" is day of year

    you probably want "d" for days
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    118
    thats strange, if its day of the year, it should not be more than 356 right?

    And i get results with exess of 800. In any case it seems to be working fine as it is.

    If I add group by (with all the fields) would i have to replace where with "having"?

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    Thanks, that worked like a charm...

    I also did a search on the net after you told me i need to do a group by and found the solution.

    http://www.w3schools.com/sql/sql_groupby.asp

    For anyone having thesame problem

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, in general, you do not replace WHERE conditions with HAVING conditions

    WHERE is used to filter out unwanted rows before they are grouped

    HAVING is used to perform tests on the groups and is best used on the aggregates, not the grouped columns

    e.g. HAVING count(*) > 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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