New to programming questions - 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

New to programming questions

Author

Topic

diablokicks
Starting Member

5 Posts

Posted - 2009-10-08 : 22:51:28

Hello, i was wondering if you could help me, i'm trying to write a beginner sql program. The database has 3 tables, Salesperson, salesOrder, and Salesperson share. The sales order has order information and total commission, and salesperson share has both primary keys from salesperson and sales order, also it has commissionpercent. Two issues i'm having is trying to show the names of the salespeople and the total and average of the order total of all orders where the sales people participated.
I have


--B
select salesperson.name, avg(Salesorder.OrderTotal) , Salesorder.OrderTotal
from salesperson, Salesorder
Group by salesperson.name, salesorder.ordertotal
where avg(salesorder.Ordertotal)>100

But i don't know how to show the average of each.

Also i need to show the sales ordernumber and the sum of the commission percent, i'm just having troubles in trying to compare for each order or sales person.

Thank you very much for all the help.

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 06:33:19

Have you normalised all your tables / columns?

(Also in a query like this you can save time by using simpler alias' (Example Select c.CaseNumber, d.DespatchDestination From CaseTable c inner join DespatchTable d)

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 06:43:23

Also, what is this-

from salesperson, Salesorder

You need to use 'JOIN' to select data from more than one table.
Or you could use 'UNION'.

Select sp.Name, avg(so.OrderTotal), so.OrderTotal
From Salesperson
Left Join SalesPersonShare sps on sps.Your_Foreign_Key_Here = sp.Your_Primary_Key_Here
Inner Join salesOrder so on so.Primary_key_Here = sps.Foreign_key_Here

Where avg(so.OrderTotal)>100

Group by sp.Name, so.OrderTotal

Feel free to ask any more questions.

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 08:22:48

The order of a query goes like so (At newb level)

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

That is the order in which SQL works. xD

[ /fail at query]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-09 : 08:34:16

quote:
Originally posted by winterh

The order of a query goes like so (At newb level)

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

That is the order in which SQL works. xD

[ /fail at query]

It is the order as you read
Actual internal order is http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

Madhivanan

Failing to plan is Planning to fail

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 08:49:22

Well, what I meant was how SQL report writing structure works... He will learn internally when the time is right! :P lol. madhivanan put me in my place. :)

[ /fail at query]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-09 : 08:53:50

quote:
Originally posted by winterh

Well, what I meant was how SQL report writing structure works... He will learn internally when the time is right! :P lol. madhivanan put me in my place. :)

[ /fail at query]

I just informed you

Madhivanan

Failing to plan is Planning to fail

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-09 : 09:15:33

Thanks :D

[ /fail at query]

diablokicks
Starting Member

5 Posts

Posted - 2009-10-11 : 13:15:51

quote:
Originally posted by winterh

Also, what is this-

from salesperson, Salesorder

You need to use 'JOIN' to select data from more than one table.
Or you could use 'UNION'.

Select sp.Name, avg(so.OrderTotal), so.OrderTotal
From Salesperson
Left Join SalesPersonShare sps on sps.Your_Foreign_Key_Here = sp.Your_Primary_Key_Here
Inner Join salesOrder so on so.Primary_key_Here = sps.Foreign_key_Here

Where avg(so.OrderTotal)>100

Group by sp.Name, so.OrderTotal

Feel free to ask any more questions.

[ /fail at query]

Here is what i have done now, and this is the result:

--B
Select salesperson.Name, avg(salesorder.OrderTotal), salesorder.OrderTotal
From Salesperson
Left Join SalesPersonShare on salespersonshare.EmployeeID = salesperson.EmployeeID
Inner Join salesOrder on salesorder.SalesOrderNumber = salespersonshare.SalesOrderNumber

Group by salesperson.Name, salesorder.OrderTotal

But the results come out wrong, it shows the "Average" and Total sales as the same value

diablokicks
Starting Member

5 Posts

Posted - 2009-10-11 : 16:41:45

quote:
Originally posted by diablokicks

quote:
Originally posted by winterh

Also, what is this-

from salesperson, Salesorder

You need to use 'JOIN' to select data from more than one table.
Or you could use 'UNION'.

Select sp.Name, avg(so.OrderTotal), so.OrderTotal
From Salesperson
Left Join SalesPersonShare sps on sps.Your_Foreign_Key_Here = sp.Your_Primary_Key_Here
Inner Join salesOrder so on so.Primary_key_Here = sps.Foreign_key_Here

Where avg(so.OrderTotal)>100

Group by sp.Name, so.OrderTotal

Feel free to ask any more questions.

[ /fail at query]

Here is what i have done now, and this is the result:
--B
Select salesperson.Name, avg(salesorder.OrderTotal), salesorder.OrderTotal
From Salesperson
Left Join SalesPersonShare on salespersonshare.EmployeeID = salesperson.EmployeeID
Inner Join salesOrder on salesorder.SalesOrderNumber = salespersonshare.SalesOrderNumber

Group by salesperson.Name, salesorder.OrderTotal

But the results come out wrong, it shows the "Average" and Total sales as the same value

I don't know how to caluculate the total sales, and then do the average of that? Instead of the average of each order

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-12 : 06:55:29

Declare @Total as int
Set @Total =
(
Select SUM(so.OrderTotal)
from SalesOrder so
)

Declare @Total1 int
Set @Total1 =
(
Select Count(*)
From SalesOrder
)

Select sp.Name, (@Total / @Total1) as 'Average', so.OrderTotal,
From Salesperson
Left Join SalesPersonShare sps on sps.Your_Foreign_Key_Here = sp.Your_Primary_Key_Here
Inner Join salesOrder so on so.Primary_key_Here = sps.Foreign_key_Here

Group by sp.Name, so.OrderTotal

Try something like that my main man. Let me know if it works. If not tweak with the syntax here: (@Total / @Total1)

[/fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-12 : 07:20:40

Declare @Total as int
Set @Total =
(
Select SUM(so.OrderTotal)
from SalesOrder so
)

ABOVE CREATES A VARIABLE HOLDING TOTAL SUM OF ALL SALESORDERS (by the way)
BELOW CREATES A VARIABLE HOLDING TOTAL COUNT OF EACH SALESORDERS (each salesorder is +1 To give a total number of them)
P.s I dont think you need the brackets around the @Total/@Total1

Declare @Total1 int
Set @Total1 =
(
Select Count(*)
From SalesOrder
)

[/fail at query]

diablokicks
Starting Member

5 Posts

Posted - 2009-10-12 : 20:33:07

quote:
Originally posted by winterh

Declare @Total as int
Set @Total =
(
Select SUM(so.OrderTotal)
from SalesOrder so
)

ABOVE CREATES A VARIABLE HOLDING TOTAL SUM OF ALL SALESORDERS (by the way)
BELOW CREATES A VARIABLE HOLDING TOTAL COUNT OF EACH SALESORDERS (each salesorder is +1 To give a total number of them)
P.s I dont think you need the brackets around the @Total/@Total1

Declare @Total1 int
Set @Total1 =
(
Select Count(*)
From SalesOrder
)

[/fail at query]

I appreciate all the help, i get that it cannot find the Total1 column? I tried both set and set as but still no luck?

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-13 : 04:11:24

Declare @Total as int
Set @Total =
(
Select SUM(so.OrderTotal)
from SalesOrder so
)

Declare @Total1 int
Set @Total1 =
(
Select Count(*)
From SalesOrder
)

Select sp.Name, @Total / @Total1 as 'Average', so.OrderTotal,
From Salesperson
Left Join SalesPersonShare sps on sps.Your_Foreign_Key_Here = sp.Your_Primary_Key_Here
Inner Join salesOrder so on so.Primary_key_Here = sps.Foreign_key_Here

Group by sp.Name, so.OrderTotal

That will work put your keys in the spaces

[/fail at query]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources