# SUM of data from count? DB2?

I am doing the following

selecting A, B and COUNT(C)
grouping by A, B
order by A, B

Want to show sum of B for A groups, how can I do this? What if I wanted to show two or three sums?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billing EngineerCommented:
can you explain with data samples what you are trying to achieve?
what database are you using?
Author Commented:
DB2.

I explained it clearly above...

selecting A, B and COUNT(C)
grouping by A, B
order by A, B

DATA IS:
A, B, C,
alpha, 10, 589724
beta, 3700, 902373
alpha, 15, 392759
alpha, 16, 235790
alpha, 32, 902759
beta, 72, 923759

RESULT SHOULD BE:
A, sum(b), count(c)
alpha, 73, 4
beta, 3772, 2

I'm not sure how to get B...
Commented:
selecting A, sum(B), COUNT(1)
grouping by A <== only A in the group by
order by A
Author Commented:

SELECT A, SUM(B), COUNT(DISTINCT C)
GROUP BY A
ORDER BY A

DATA IS:
alpha, 10, 589724
beta, 3700, 902373
alpha, 15, 392759
alpha, 16, 235790
alpha, 32, 902759
beta, 72, 923759
beta, 9000, 923859

RESULT SHOULD BE:
alpha, 73, 4
beta, 12772, 2
Commented:
Why using COUNT(DISTINCT C) in the request if you like only count the number of recods !?
yu can use in the count : COUNT(1) or COUNT(*) or COUNT(C)
selecting A, sum(B), COUNT(1)
grouping by A
order by A
The result will be as you like.
Author Commented:
I'm sorry but I don't understand your reply, could you use more clear english?

I'm using count distinct because I want the count of C but I don't want to count duplicates.
Commented:
Try this one :

select A, sum(B), (select COUNT(distinct C) from TAB T2 where T2.A=TAB.A)
from TAB
group by A
order by A

Author Commented:
What is "TAB"?

Here is my query as it stands right now

SELECT "SEG"."SEG_DESCRIPTION", "MEMBERSHIP"."MISC1", "MEMBERSHIP"."MISC2", count(DISTINCT "MEMBERSHIP"."MEMBER_NBR") as count,
sum(SHARE.BALANCE) as BALANCE, SUM(AVG_MONTHLY1_BAL) as AVG_MNTHLY_BALANCE
FROM   "DB2INST1"."MEMBERSHIP" "MEMBERSHIP"
LEFT OUTER JOIN "DB2INST1"."SEG" "SEG" ON "MEMBERSHIP"."CURRENT_AFFILIATION"="SEG"."SEG_CODE"
INNER JOIN "DB2INST1"."SHARE" "SHARE"  ON  "MEMBERSHIP"."MEMBER_NBR"="SHARE"."MEMBER_NBR"
WHERE  "MEMBERSHIP"."BRANCH"<>63 AND "SHARE"."CLOSED" = 0
GROUP BY SEG.SEG_DESCRIPTION,MEMBERSHIP.MISC2,MEMBERSHIP.MISC1
ORDER by SEG_DESCRIPTION asc, COUNT desc
Author Commented:
Well I guess lets delete this question because I never received an answer and ended up figuring it out on my own.
Author Commented:
SELECT "SEG"."SEG_DESCRIPTION", "MEMBERSHIP"."MISC1", "MEMBERSHIP"."MISC2", count(DISTINCT "MEMBERSHIP"."MEMBER_NBR") as count,
sum(SHARE.BALANCE) as BALANCE, SUM(AVG_MONTHLY1_BAL) as AVG_MNTHLY_BALANCE
FROM   "DB2INST1"."MEMBERSHIP" "MEMBERSHIP"
LEFT OUTER JOIN "DB2INST1"."SEG" "SEG" ON "MEMBERSHIP"."CURRENT_AFFILIATION"="SEG"."SEG_CODE"
INNER JOIN "DB2INST1"."SHARE" "SHARE"  ON  "MEMBERSHIP"."MEMBER_NBR"="SHARE"."MEMBER_NBR"
WHERE  "MEMBERSHIP"."BRANCH"<>63 AND "SHARE"."CLOSED" = 0
GROUP BY SEG.SEG_DESCRIPTION,MEMBERSHIP.MISC2,MEMBERSHIP.MISC1
ORDER by SEG_DESCRIPTION asc, COUNT desc

Distinct ended up being the correct method to not show the record twice but to use it in creating the sums.
Commented:
Closed, 250 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by