Query grouping and percentage by group

Posted on 2007-07-20
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
Question by:lexo
    LVL 15

    Expert Comment

    try this:

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

    Expert Comment

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

    Author Comment

    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?
    LVL 15

    Expert Comment

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

    Author Comment

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

    Accepted Solution

    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?

    Author Comment

    high 5

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle SQL Query Syntax 6 71
    Remove Hyphens in Oracle SQL 5 35
    Case Statement in SQL Delminted Query 9 48
    Format Number Field 10 34
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now