Sum individual counts

  • Thread starter Thread starter Vincent
  • Start date Start date
V

Vincent

Hello:
I have a table like this one
Dept Location Count
A U 1
B V 1
B W 2
B X 3
C Y 0
....
How can I write a query that will sum the Count based on
each Dept regardless of locations.
Thanks in advance!
Vincent
 
Vincent said:
Hello:
I have a table like this one
Dept Location Count
A U 1
B V 1
B W 2
B X 3
C Y 0
...
How can I write a query that will sum the Count based on
each Dept regardless of locations.
Thanks in advance!
Vincent
Click to expand...

CREATE TABLE MyTable_032604_2
(MyTableID INTEGER
,Dept CHAR(1)
,Location CHAR(1)
,[Count] INTEGER
,CONSTRAINT pk_MyTable_032604_2 PRIMARY KEY (MyTableID)
)

Sample Data

1, A, U, 1
2, B, V, 1
3, B, W, 2
4, B, X, 3
5, C, Y, 0

SELECT M1.Dept
,SUM(M1.[Count]) as SumOfCount
FROM MyTable_032604_2 as M1
GROUP BY M1.Dept

Output
Dept, SumOfCount
A 1
B 6
C 0


Sincerely,

Chris O.
 
Thanks a lot!
Vincent
-----Original Message-----

Hello:
I have a table like this one
Dept Location Count
A U 1
B V 1
B W 2
B X 3
C Y 0
...
How can I write a query that will sum the Count based on
each Dept regardless of locations.
Thanks in advance!
Vincent
Click to expand...

CREATE TABLE MyTable_032604_2
(MyTableID INTEGER
,Dept CHAR(1)
,Location CHAR(1)
,[Count] INTEGER
,CONSTRAINT pk_MyTable_032604_2 PRIMARY KEY (MyTableID)
)

Sample Data

1, A, U, 1
2, B, V, 1
3, B, W, 2
4, B, X, 3
5, C, Y, 0

SELECT M1.Dept
,SUM(M1.[Count]) as SumOfCount
FROM MyTable_032604_2 as M1
GROUP BY M1.Dept

Output
Dept, SumOfCount
A 1
B 6
C 0


Sincerely,

Chris O.


.
Click to expand...
 
Vincent,
Gotcha help here. Set up a query including these
fields, in the same order. You can also exclude the
Location field if you wish; then, click View-Totals. This
will open up the Totals row in the query. Under the Dept
field, in the Totals row, click the drop-down, and select
Group By. Then, in the Totals row of the Count field,
select Sum. Run the query, and it will show the various
depts, with the sum of the counts field.
Bernie
 
Back
Top