## Percent of total???

 Author Message SQLNube Grasshopper Group: General Forum Members Points: 18 Visits: 9 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 layoutcolumn names - chargeDescription, charge, weightExample data - wrong weight, \$2.00, 20lbs wrong address, \$5.00, 2lbs wrong address, \$5.00, 3lbs wrong weight, \$3.00, 10lbsMy resultset needs to be something similar to:ChargeDescription, sumOfCharges, PercentOfTotalCharges, averageWeightwrong address, \$10.00, 66%,2.5lbswrong weight, \$5.00, 33%, 15lbsAny help appreciated! Have a good 4th! Sean Lange SSC Guru Group: General Forum Members Points: 149043 Visits: 18575 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 layoutcolumn names - chargeDescription, charge, weightExample data - wrong weight, \$2.00, 20lbs wrong address, \$5.00, 2lbs wrong address, \$5.00, 3lbs wrong weight, \$3.00, 10lbsMy resultset needs to be something similar to:ChargeDescription, sumOfCharges, PercentOfTotalCharges, averageWeightwrong address, \$10.00, 66%,2.5lbswrong weight, \$5.00, 33%, 15lbsAny 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 / Countgroup 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.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) SQLNube Grasshopper Group: General Forum Members Points: 18 Visits: 9 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 subqueryI'm thinking I need to break this out into two subqueries? Sean Lange SSC Guru Group: General Forum Members Points: 149043 Visits: 18575 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 AvgWeightfrom Detailsgroup by ChargeDescriptionorder 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 AvgWeightfrom Table1group 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.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)