Disctinct query plus total column value

[seanruk]

Hi, hopefully someone can help me with this query. Basically I have 3 columns in 1 table, they are:

NAME
SIZE
NUMBER

I need a query that will return the NAME and SIZE as distinct values and then add the NUMBER together to give a total. An example of the results before the query would be:

NAME SIZE NUMBER
Joe A 1
Stan A 6
Joe B 3
Joe C 12
Stan B 1
Joe A 3
Stan C 2
Joe B 2
Stan A 2

Using the following query will return the distinct values:

SELECT DISTINCT Name, Size
FROM Table1
ORDER BY Name, Size

Results will be:

Joe A
Joe B
Joe C
Stan A
Stan B
Stan C

What I am trying to do now is take the NUMBER for each entry and total that number so the results will be:

Joe A 4
Joe B 5
Joe C 12
Stan A 8
Stan B 1
Stan C 2

Your help will be much appreciated.

Sean

[Rabbit]

What you're looking for is an aggregate query, there's no need for a distinct.

SELECT groupingField, SUM(valueField)
FROM someTable
GROUP BY groupingField

[seanruk]

Thank you for your quick reply. I will test this and let you know how I get on but it seems so obvious now :o)

[seanruk]

That was the answer, thanks