Percent of total???

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Microsoft Access » Microsoft Access » Percent of total???

Percent of total???

Rate Topic

Display Mode

Topic Options

Author

Message

SQLNube

Posted Tuesday, July 03, 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 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!

Post #1324622

Sean Lange

Posted Tuesday, July 03, 2012 1:03 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233

SQLNube (7/3/2012)

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1324625

SQLNube

Posted Tuesday, July 03, 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 subquery

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

Post #1324632

Sean Lange

Posted Tuesday, July 03, 2012 1:50 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 8,592, Visits: 8,233

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1324656

« Prev Topic | Next Topic »

Permissions