Solved

SUM of data from count? DB2?

Posted on 2006-04-20
Medium Priority
646 Views
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?
0
Question by:qbn321

LVL 143

Expert Comment

ID: 16498579
can you explain with data samples what you are trying to achieve?
what database are you using?
0

Author Comment

ID: 16499093
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

LVL 5

Expert Comment

ID: 16502987
selecting A, sum(B), COUNT(1)
grouping by A <== only A in the group by
order by A
0

Author Comment

ID: 16507536

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

LVL 5

Expert Comment

ID: 16507594
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

Author Comment

ID: 16507652
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

LVL 5

Expert Comment

ID: 16507833
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

Author Comment

ID: 16507871
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

Author Comment

ID: 16587615
Well I guess lets delete this question because I never received an answer and ended up figuring it out on my own.
0

Author Comment

ID: 16587651
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

Accepted Solution

GranMod earned 0 total points
ID: 16620481
Closed, 250 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance andā¦
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demā¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. ā¦
Suggested Courses
Course of the Month13 days, 10 hours left to enroll