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?
qbn321Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you explain with data samples what you are trying to achieve?
what database are you using?
qbn321Author 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...
morisceCommented:
selecting A, sum(B), COUNT(1)
grouping by A <== only A in the group by
order by A
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

qbn321Author Commented:
What about this, will this still be correct? Does the distinct count prevent the 9000 from being added?

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
morisceCommented:
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.
qbn321Author 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.
morisceCommented:
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

qbn321Author 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
qbn321Author Commented:
Well I guess lets delete this question because I never received an answer and ended up figuring it out on my own.
qbn321Author 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.
GranModCommented:
Closed, 250 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.