?
Solved

SUM of data from count? DB2?

Posted on 2006-04-20
12
Medium Priority
?
646 Views
Last Modified: 2008-01-09
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
Comment
Question by:qbn321
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16498579
can you explain with data samples what you are trying to achieve?
what database are you using?
0
 

Author Comment

by:qbn321
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

by:morisce
ID: 16502987
selecting A, sum(B), COUNT(1)
grouping by A <== only A in the group by
order by A
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:qbn321
ID: 16507536
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
 
LVL 5

Expert Comment

by:morisce
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

by:qbn321
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

by:morisce
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

by:qbn321
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

by:qbn321
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

by:qbn321
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

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Video by: Steve
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

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question