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?
 
GranModCommented:
Closed, 250 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you explain with data samples what you are trying to achieve?
what database are you using?
0
 
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...
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
morisceCommented:
selecting A, sum(B), COUNT(1)
grouping by A <== only A in the group by
order by A
0
 
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
0
 
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.
0
 
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.
0
 
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

0
 
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
0
 
qbn321Author Commented:
Well I guess lets delete this question because I never received an answer and ended up figuring it out on my own.
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.