asked on

Query grouping and percentage by group

I am trying to get a query to show mothly percentages by groups.

Branch Type
1 A
2 A
1 C
4 B
5 D

I want a query that will show
Branch 1 Type: A Percent: 20%
Branch 1 Type: B Percent: 40%
Branch 1 Type: C Percent: 20%
Branch 1 Type: D Percent: 20%

Branch 2 Type: A Percent: 40%
Branch 2 Type: B Percent: 20%
Branch 2 Type: C Percent: 20%
Branch 2 Type: D Percent: 20%

Branch 3 Type: A Percent: 40%
Branch 3 Type: B Percent: 20%
Branch 3 Type: C Percent: 20%
Branch 3 Type: D Percent: 20%

Where the percentage is of the total belonging to each branch.
I am using SQL2000

derekkromm

try this:

select branch, type, sum(yournumber)/(select sum(yournumber) from table where branch=t.branch)
from table t
group by branch, type

Guy Hengel [angelIII / a3]

I don't see how the input data can result in the output data?

lexo

ASKER

The yournumber part is going to have to be the count of the records applying to that branch.
Angelll, the only data in the output is in the input. Maybe I didnt explain it clearly?
There are three branches. There are four types of each record/row.
I need to know what percentage each branch has of each type. So of Branch 1's total number of rows, 80% is type A, 10% is type B, 5% is type C, etc. Of Branch 2's records, 20% is type A, 60% is type B, etc.

Does that make sense?
Will a union work?

derekkromm

select branch, type, count(*)/(select count(*) from table where branch=t.branch)
from table t
group by branch, type

lexo

ASKER

Almost there...except that the 3rd column is all zeros.

ASKER CERTIFIED SOLUTION

derekkromm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lexo

ASKER

high 5