?
Solved

Query grouping and percentage by group

Posted on 2007-07-20
7
Medium Priority
?
203 Views
Last Modified: 2011-09-20
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
Comment
Question by:lexo
  • 3
  • 3
7 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 19533817
try this:

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19533820
I don't see how the input data can result in the output data?
0
 

Author Comment

by:lexo
ID: 19534860
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Expert Comment

by:derekkromm
ID: 19534872
select branch, type, count(*)/(select count(*) from table where branch=t.branch)
from table t
group by branch, type
0
 

Author Comment

by:lexo
ID: 19535074
Almost there...except that the 3rd column is all zeros.
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 19535098
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
 

Author Comment

by:lexo
ID: 19535159
high 5
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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 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