Can someone please help me resolve the following -
I have a table consisting of sales orders information with salesmen who lead
the order.
Now I have a need to find average by adding individual salesman's records
and then dividing it by total no. of salesmen in the table.
Ex. Saleman No., Order No.
1, 1
1, 2
2,3
1,4
3,5
3,6
In this case the output should be something like
Salesman #orders
1 3
2 1
3 2
Average = #total no. of orders / #total no. of salesmen
my query is -
select firstname, lastname, count(*) from project
where
ISNULL(CompleteFLAG,'N') ='N' )
group by firstname, lastname
order by firstname
I would like to get the average by adding count(*) for each person/#total
no. of persons.
Thank you,
-MeTry something like this.
select distinct(Saleman No),count(Order No) as OrderNo from vendor
group by Saleman No
Maninder
MCDBA|||On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> Can someone please help me resolve the following -
> I have a table consisting of sales orders information with salesmen who lead
> the order.
> Now I have a need to find average by adding individual salesman's records
> and then dividing it by total no. of salesmen in the table.
> Ex. Saleman No., Order No.
> 1, 1
> 1, 2
> 2,3
> 1,4
> 3,5
> 3,6
> In this case the output should be something like
> Salesman #orders
> 1 3
> 2 1
> 3 2
> Average = #total no. of orders / #total no. of salesmen
> my query is -
> select firstname, lastname, count(*) from project
> where
> ISNULL(CompleteFLAG,'N') ='N' )
> group by firstname, lastname
> order by firstname
> I would like to get the average by adding count(*) for each person/#total
> no. of persons.
> Thank you,
> -Me
It seems like your query contradicts the sample data you used. What is
the key of the Project table? Do you really store the sales person's
name next to each order? Does every order have a sales person
associated with it? Please always include DDL so that we don't have to
guess these things.
Perhaps there are sales people who haven't yet got any orders to their
names? In that case you'll need a SalesPerson table (which I would
hope you have in any case). Here's one possibility:
SELECT COUNT(*)
/(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
FROM Project;
If you just want to include those who have sales, try:
SELECT COUNT(*)
/(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
FROM Project) AS AvgSales
FROM Project;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thanks for the reply!
Your solution helped me resolve my issue.
Appreciate your help!
-Me
"David Portas" wrote:
> On 13 Feb, 16:09, Me <M...@.discussions.microsoft.com> wrote:
> > Can someone please help me resolve the following -
> >
> > I have a table consisting of sales orders information with salesmen who lead
> > the order.
> >
> > Now I have a need to find average by adding individual salesman's records
> > and then dividing it by total no. of salesmen in the table.
> >
> > Ex. Saleman No., Order No.
> > 1, 1
> > 1, 2
> > 2,3
> > 1,4
> > 3,5
> > 3,6
> >
> > In this case the output should be something like
> >
> > Salesman #orders
> > 1 3
> > 2 1
> > 3 2
> >
> > Average = #total no. of orders / #total no. of salesmen
> >
> > my query is -
> >
> > select firstname, lastname, count(*) from project
> > where
> > ISNULL(CompleteFLAG,'N') ='N' )
> > group by firstname, lastname
> > order by firstname
> >
> > I would like to get the average by adding count(*) for each person/#total
> > no. of persons.
> >
> > Thank you,
> > -Me
> It seems like your query contradicts the sample data you used. What is
> the key of the Project table? Do you really store the sales person's
> name next to each order? Does every order have a sales person
> associated with it? Please always include DDL so that we don't have to
> guess these things.
> Perhaps there are sales people who haven't yet got any orders to their
> names? In that case you'll need a SalesPerson table (which I would
> hope you have in any case). Here's one possibility:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(*) AS REAL) FROM SalesPerson) AS AvgSales
> FROM Project;
> If you just want to include those who have sales, try:
> SELECT COUNT(*)
> /(SELECT CAST(COUNT(DISTINCT SalesPersonNo) AS REAL)
> FROM Project) AS AvgSales
> FROM Project;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
>|||Hi Maninder,
Thanks for the reply!
Actually I was looking for what David has in his thread.
But appreciate your keeping my tempo going.
Thank you again for your help!
-Me
"Maninder" wrote:
> Try something like this.
> select distinct(Saleman No),count(Order No) as OrderNo from vendor
> group by Saleman No
> Maninder
> MCDBA
>
