Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Percent of total??? Expand / Collapse
Author
Message
Posted Tuesday, July 3, 2012 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, July 3, 2012 1:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, July 3, 2012 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1324656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse