lexo
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
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
I don't see how the input data can result in the output data?
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?
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?
select branch, type, count(*)/(select count(*) from table where branch=t.branch)
from table t
group by branch, type
from table t
group by branch, type
ASKER
Almost there...except that the 3rd column is all zeros.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
high 5
select branch, type, sum(yournumber)/(select sum(yournumber) from table where branch=t.branch)
from table t
group by branch, type