can you explain with data samples what you are trying to achieve?

what database are you using?

what database are you using?

Solved

Posted on 2006-04-20

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?

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?

11 Comments

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...

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

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.

I'm using count distinct because I want the count of C but I don't want to count duplicates.

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

from TAB

group by A

order by A

Here is my query as it stands right now

SELECT "SEG"."SEG_DESCRIPTION", "MEMBERSHIP"."MISC1", "MEMBERSHIP"."MISC2", count(DISTINCT "MEMBERSHIP"."MEMBER_NBR")

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_AFFI

INNER JOIN "DB2INST1"."SHARE" "SHARE" ON "MEMBERSHIP"."MEMBER_NBR"=

WHERE "MEMBERSHIP"."BRANCH"<>63 AND "SHARE"."CLOSED" = 0

GROUP BY SEG.SEG_DESCRIPTION,MEMBER

ORDER by SEG_DESCRIPTION asc, COUNT desc

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_AFFI

INNER JOIN "DB2INST1"."SHARE" "SHARE" ON "MEMBERSHIP"."MEMBER_NBR"=

WHERE "MEMBERSHIP"."BRANCH"<>63 AND "SHARE"."CLOSED" = 0

GROUP BY SEG.SEG_DESCRIPTION,MEMBER

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.

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.

Read about achieving the basic levels of HRIS security in the workplace.

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

Connect with top rated Experts

**19** Experts available now in Live!