How can calculate Sum?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a table with 4 fields:
ID,DeptName,Store1,Store2
1 Dp1 1 2
2 Dp1 1
3 Dp1 2 1
4 Dp1 3 2
How can I calculate SUM for Dp1 base on Store1 is 4 and SUM for Dp1 for
Store2 is 3.
Thank you very much.
MN
 
Well, I can't get those sums looking at your posted example. What did you
add to get Dp1 Store1 is 4 and for store2 is 3?

SELECT DeptName, Sum(Store1) as Store1Sum, Sum(Store2) as Store2Sum
FROM Table

That would return
DP1 7 5
For DeptName, Store1, and Store2
 
Hi John,
I am appology for not explain very clearly.
I am Count for Dp1 Store1 is 4 and Store2 is 3. Sorry.
Any advice?
Regards, MN.
 
Same query replace SUM with Count

SELECT DeptName, Count(Store1) as Store1Count,
Count(Store2) as Store2Count
FROM Table

IF you get 4 and 4 then the fields must be storing zero-lenght strings.
Post back if that is the case, as there is a way to get the count in that
case also.
 
What is the data type of Store1 and Store2? Is this a number field or is it a
text field that is storing number characters?

SELECT DeptName, Abs(Sum(Val(Store1)>0)) as Store1Count,
Abs(Sum(Val(Store2)>0)) as Store2Count
FROM Table
GROUP BY DeptName

I noticed that I left off the Group By in the earlier queries. That should have
caused an error, so I assume that you took care of that problem yourself.
 
Back
Top