Subquery Question: a part divided by its whole from the same table

[MikeNext]

Hello,


I am new to SQL and what I would like to do is fairly easy to explain and hopefully fairly easy to do. Essentially, I have sales people that sell products. In a single table it tells me how many products per state they have sold.

I would like to know how much per salesperson is sold per state as a %.

So, if the data looks like this in tblSales:

Person | State | Qty
John | Texas | 50
John | New York | 25
John | California | 10
Sally | Texas | 200

Then I *think* my query should look something like this ??? :

SELECT
tblSales.Person,
tblState.State,
SUM(tblSales.Qty),
(
SELECT
SUM(tblSales.Qty) as AllSales
FROM tblSales
GROUPBY tblSales.Person
)
FROM tblSales
GROUPBY tblSales.Person,tblState.State;

But, this is not working... the above is not the actual query I am using, but it does represent the exact structure I am trying to figure out.

And, this should result in output that looks like the below, which I will then add a calculation to on the end to produce the last row:

Person | State | Qty | AllSales | %
John | Texas | 50 | 85 | 59%
John | New York | 25 | 85 | 29%
John | California | 10 | 85 | 12%
Sally | Texas | 200 | 200 | 100%

At this point, I am not sure that I using the correct approach. I have been going in circles for more time that I care to admit on this one. While I know that this is very simple stuff for most of you, it is driving this newbie up the wall....

I appreciate any help you might provide.

Thanks again,
Mike

[dcrake]

Firstly I would create a seperate query that groups by state and sums the quatity as being 100%

Then in you other query group by salesperson by state. Create a join between the state summary and the salespersons state.

Then calculate the total sales by salesperson by state against the total sales for the state to get your %

David