July 3, 2012 at 12:58 pm
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!
July 3, 2012 at 1:03 pm
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/
July 3, 2012 at 1:13 pm
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?
July 3, 2012 at 1:50 pm
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