Simple Column Totaling

[sirace]

I have a query called "manager_total" which has 6 columns: manager, rep_no, count1, count2, count3, count4. Sample data will looking the following

manager     rep_no     count1    count2    count3   count4
----------------------------------------------------------
john doe       1           10         7         2        1
john doe       2           20        15         4        1
john doe       3           23        20         0        3
jane doe       4            4         3         1        0
jane doe       5           14         3         1       10
jane doe       6            9         3         3        3
jack smith     7           15        11         4        0
jack smith     8           16         1        14        1
jack smith     9            3         3         0        0

Basically, I need the total for each count column per manager. I'd like the result to look like the following:

manager      count1    count2    count3   count4
------------------------------------------------
john doe         53        42         6        5
jane doe         27         9         5       13
jack smith       34        15        18        1

However, when I run the query I've set up, I get some interesting results. It seems to be concatentating my numbers together instead of summing them. So instead of what I want I'll end up getting the following:

manager     count1    count2    count3   count4
-----------------------------------------------
john doe    102023     71520       240      113
jane doe      4149       333       113     0103
jack smith   15163      1113      4140      010

All the count fields are from another query, so I'm not sure if that is part of the problem or not.


Any ideas?

 

[AmritPaulSingh]

Try this out

SELECT manager, sum(count1) as TotalCount1, sum(count2) as TotalCount2, sum(count3) as TotalCount3, sum(count4) as TotalCount4 FROM yourTable
GROUP BY manager, count1, count2, count3;

 

[sirace]

Thanks alot. I took what you said and tried it and it worked. I would have included my original code, but it would have just confused matters at the queries and tables were a bit more complicated than what I had posted. Thanks again!

 

[Golom]

Looks like the contents of the "Count#" fields are not being treated as numbers. For example, the values for Count2 for John Doe are 7, 15 and 20 and your result is 71520 (i.e. concatenation not addition). Your SQL should be something like

Select manager, Sum(Count1) As C1,
                Sum(Count2) As C2,
                Sum(Count3) As C3,
                Sum(Count4) As C4
From tbl

Group By manager

 

[sirace]

Yah, it's odd, because I later tried to concatenate some text using the sum function and it didn't work the same way. Go figure!