none
Adding Sum in a query RRS feed

  • Question

  • My Query below:

    SELECT tblDaily_ItMdt.HorseID, tblDaily_ItMdt.TotalDays
    FROM tblDaily_ItMdt;

    How can I add HorseID (130) together to get a total of 31 TotalDays

    Thanks for any help................Bob


    xxx

    Thursday, June 1, 2017 2:15 AM

Answers

  • SELECT intermediate, dtDate, HorseID,StartDate,EndDate, SUM(TotalDays), DailyCharge

    from Tablename

    GROUP BY HorseID

    this will work


    Sorry, but it won't.  You cannot include columns in the SELECT clause which are not in the GROUP BY clause, or the subject of an aggregation operation.  The query would need to be:

    SELECT HorseID,
    SUM(tblDaily_ItMdt.TotalDays) AS TotalDays
    FROM tblDaily_ItMdt
    GROUP BY HorseID;

    Note BTW that to return an aggregated column with the same column heading as the original column the column name within the aggregation operation must be qualified by the table name.


    Ken Sheridan, Stafford, England

    • Marked as answer by TurnipOrange Thursday, June 1, 2017 10:14 PM
    Thursday, June 1, 2017 9:59 PM

All replies

  • Hi Bob,

    If you use a Totals query, you can get the 31 days you want, but if you're saying you want 31 to show up in the two records in your query, then you can use a domain aggregate function like DSum().

    The Totals query will only work if you don't need to display all the other fields in the query result because grouping the records works better when you only use the minimum number of fields in the query to allow grouping them properly.

    Hope it helps...

    Thursday, June 1, 2017 3:18 AM
  • SELECT intermediate, dtDate, HorseID,StartDate,EndDate, SUM(TotalDays), DailyCharge

    from Tablename

    GROUP BY HorseID

    this will work

    Thursday, June 1, 2017 6:48 AM
  • SELECT intermediate, dtDate, HorseID,StartDate,EndDate, SUM(TotalDays), DailyCharge

    from Tablename

    GROUP BY HorseID

    this will work


    Sorry, but it won't.  You cannot include columns in the SELECT clause which are not in the GROUP BY clause, or the subject of an aggregation operation.  The query would need to be:

    SELECT HorseID,
    SUM(tblDaily_ItMdt.TotalDays) AS TotalDays
    FROM tblDaily_ItMdt
    GROUP BY HorseID;

    Note BTW that to return an aggregated column with the same column heading as the original column the column name within the aggregation operation must be qualified by the table name.


    Ken Sheridan, Stafford, England

    • Marked as answer by TurnipOrange Thursday, June 1, 2017 10:14 PM
    Thursday, June 1, 2017 9:59 PM
  • Thanks Ken That worked............Bob

    xxx

    Thursday, June 1, 2017 10:15 PM