• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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
0
lexo
Asked:
lexo
  • 3
  • 3
1 Solution
 
derekkrommCommented:
try this:

select branch, type, sum(yournumber)/(select sum(yournumber) from table where branch=t.branch)
from table t
group by branch, type
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't see how the input data can result in the output data?
0
 
lexoAuthor Commented:
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?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
derekkrommCommented:
select branch, type, count(*)/(select count(*) from table where branch=t.branch)
from table t
group by branch, type
0
 
lexoAuthor Commented:
Almost there...except that the 3rd column is all zeros.
0
 
derekkrommCommented:
select branch, type, cast(count(*) as numeric(6,2))/cast((select count(*) as numeric(6,2)) from table where branch=t.branch)
from table t
group by branch, type

how about that?
0
 
lexoAuthor Commented:
high 5
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now