Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Percent of total??? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, July 3, 2012 12:58 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, July 31, 2012 9:58 AM Points: 2, 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!
Post #1324622
 Posted Tuesday, July 3, 2012 1:03 PM
 SSCoach Group: General Forum Members Last Login: Today @ 9:55 AM Points: 16,141, Visits: 16,844
 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 Moden's 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)
Post #1324625
 Posted Tuesday, July 3, 2012 1:13 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, July 31, 2012 9:58 AM Points: 2, 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?
Post #1324632
 Posted Tuesday, July 3, 2012 1:50 PM
 SSCoach Group: General Forum Members Last Login: Today @ 9:55 AM Points: 16,141, Visits: 16,844
 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 Moden's 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)
Post #1324656

 Permissions