I have a simple table from two fields, words, frequency...
- Words Frequncy
- ABC 5
- DEF 7
- GHI 9
- ABC 3
- DEF 2
- GHI 1
The words are repeated with different frequencies and I want to sum the Frequncy values for each word to
- ABC 8
- DEF 9
- GHI 10
in a query.
What you need is a GROUP BY clause
SELECT Words, SUM(frequency) AS TotalFrequency
FROM the_table
GROUP BY Words
ORDER BY Words;
The GROUP BY clause must list all the columns used in the select list to which no aggregate function (like MIN, MAX, AVG or SUM) is applied.
The column name generated for expressions is not defined. In Access, for instance, it depends on the language version of Access. The name might be SumOfFrequency in English but SummeVonFrequency in German. An application working on one PC might fail on another one. Therefore I suggest defining a column name explicitly with expr AS column_name.
You need a group by clause. What this clause does is to, for lack of a better word, group the result for each distinct value in the column(s) specified in it. Then, aggregate functions (like sum) can be applied separately for each group. So, in your use case, you'd want to group the rows per value in Words and then sum each group's Frequency:
SELECT Words, SUM(Frequency)
FROM MyTable
GROUP BY Words
group by. Mea culpa.