Percent of total???

  • I am in need of providing a query that gives a subtotal of a charge, the percent of total charges, the weight of an item, and the average weight of an item. I'm pretty sure I can get all of this info in subqueiries or multiple queries but I wanted to see if I could do it in one?

    table layout

    column names - chargeDescription, charge, weight

    Example data - wrong weight, $2.00, 20lbs

    wrong address, $5.00, 2lbs

    wrong address, $5.00, 3lbs

    wrong weight, $3.00, 10lbs

    My resultset needs to be something similar to:

    ChargeDescription, sumOfCharges, PercentOfTotalCharges, averageWeight

    wrong address, $10.00, 66%,2.5lbs

    wrong weight, $5.00, 33%, 15lbs

    Any help appreciated! Have a good 4th!

  • SQLNube (7/3/2012)


    I am in need of providing a query that gives a subtotal of a charge, the percent of total charges, the weight of an item, and the average weight of an item. I'm pretty sure I can get all of this info in subqueiries or multiple queries but I wanted to see if I could do it in one?

    table layout

    column names - chargeDescription, charge, weight

    Example data - wrong weight, $2.00, 20lbs

    wrong address, $5.00, 2lbs

    wrong address, $5.00, 3lbs

    wrong weight, $3.00, 10lbs

    My resultset needs to be something similar to:

    ChargeDescription, sumOfCharges, PercentOfTotalCharges, averageWeight

    wrong address, $10.00, 66%,2.5lbs

    wrong weight, $5.00, 33%, 15lbs

    Any help appreciated! Have a good 4th!

    I am not an Access master but the basic concept is the same.

    Description,

    Sum,

    Sum / (SubQuery for Total Sum of all groups),

    Sum / Count

    group by Description

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • First...thank you for the prompt reply!

    But I am receiving an error:

    cannot perform an aggregate function on an expression containing an aggregate or a subquery

    I'm thinking I need to break this out into two subqueries?

  • No need for that. Here is some code that works perfectly in SQL.

    ;with Details(ChargeDescription, Charge, Weight)

    as

    (

    select 'wrong address', 5.00, 2 union all

    select 'wrong address', 5.00, 3 union all

    select 'wrong weight', 3.00, 10

    )

    select ChargeDescription, SUM(Charge) as TotalCharges,

    (SUM(Charge) / (select SUM(Charge) from Details)) * 100 as PercentOfTotal,

    SUM(Weight) / (COUNT(*) * 1.0) as AvgWeight

    from Details

    group by ChargeDescription

    order by ChargeDescription

    Just to confirm I created an Access database with a table named Table1. I used the same column names and the same data.

    Here is the query that worked perfectly in Access.

    SELECT Description, SUM(Charge) as TotalCharges,

    SUM(Charge) / (select SUM(Charge) from Table1) as PercentOfTotal,

    SUM(Weight) / COUNT(*) as AvgWeight

    from Table1

    group by Description

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply