Total Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Total Query

Author

Topic

MrSmith
Starting Member

5 Posts

Posted - 2013-07-18 : 14:11:06

I am building a query based on a table. The only new field I wish to add is a "WeightFactor" field that is calculated as a percentage of the total TotalPrice column. Basically, I want a new column to display [TotalPrice]/Sum([TotalPrice]), so it gives me a percentage answer such as 2.00%. When I run the query the new field is simply showing as 100% for each row, what I want it to be is a small % of 100%. Research online suggested I have to Group By all fields except the one I wish to total, in this case TotalPrice. Is anyone able to see where I might be going wrong?

SELECT [2-C: Avg Prices by Customer Number Query].[Part Number], [2-C: Avg Prices by Customer Number Query].[Customer Name], [2-C: Avg Prices by Customer Number Query].[Customer Number], [2-C: Avg Prices by Customer Number Query].SumOfQuantity, [2-C: Avg Prices by Customer Number Query].TotalCost, [2-C: Avg Prices by Customer Number Query].TotalPrice, [2-C: Avg Prices by Customer Number Query].[Gross Margin], [TotalPrice]/Sum([TotalPrice]) AS WeightFactor
FROM [2-C: Avg Prices by Customer Number Query]
GROUP BY [2-C: Avg Prices by Customer Number Query].[Part Number], [2-C: Avg Prices by Customer Number Query].[Customer Name], [2-C: Avg Prices by Customer Number Query].[Customer Number], [2-C: Avg Prices by Customer Number Query].SumOfQuantity, [2-C: Avg Prices by Customer Number Query].TotalCost, [2-C: Avg Prices by Customer Number Query].TotalPrice, [2-C: Avg Prices by Customer Number Query].[Gross Margin];

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-18 : 14:16:01

Remove the group by and use windowing (the OVER() clause) as shown below. The multiplication by 1.0 is to force floating point division, just in case your TotalPrice is of type integer.

SELECT  [2-C: Avg Prices by Customer Number Query].[Part Number] ,
        [2-C: Avg Prices by Customer Number Query].[Customer Name] ,
        [2-C: Avg Prices by Customer Number Query].[Customer Number] ,
        [2-C: Avg Prices by Customer Number Query].SumOfQuantity ,
        [2-C: Avg Prices by Customer Number Query].TotalCost ,
        [2-C: Avg Prices by Customer Number Query].TotalPrice ,
        [2-C: Avg Prices by Customer Number Query].[Gross Margin] ,
        1.0 * [TotalPrice] / SUM([TotalPrice]) OVER() AS WeightFactor
FROM    [2-C: Avg Prices by Customer Number Query]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-18 : 14:17:23

As an aside, often times people alias the tables like shown below to avoid the retyping of long table names - which is easier on the fingers and on the eyes

SELECT  a.[Part Number] ,
        a.[Customer Name] ,
        a.[Customer Number] ,
        a.SumOfQuantity ,
        a.TotalCost ,
        a.TotalPrice ,
        a.[Gross Margin] ,
        1.0 * [TotalPrice] / SUM([TotalPrice]) OVER() AS WeightFactor
FROM    [2-C: Avg Prices by Customer Number Query] AS a;

MrSmith
Starting Member

5 Posts

Posted - 2013-07-18 : 14:41:13

Thanks James, great advice! It's probably painfully obvious how new I am to this. I'm using Access 2007, which may have been important to mention. When I put in the code I am getting a "missing operator error" at the "OVER" clause. I checked online and see a bunch of examples where simply OVER () is recommended, as you did. I've been told Access is not great medium for using SQL, do you think that is the problem?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-18 : 15:07:17

Access! I am out of my league there. I am almost certain that the OVER() clause is not supported in Access. Let us see if there are any Access experts that might be able to offer suggestions here. If you don't get responses, you might want to ask in an Access forum. This forum has lot of experts on Microsoft SQL Server, but very few in Access, from what I have seen.

MrSmith
Starting Member

5 Posts

Posted - 2013-07-18 : 15:22:28

Thanks for the help, funny I told a programming friend I was building a database in Access and the first step he gave me was to stop using Access.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources