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