By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Trying to form these Queries

P: 5
Hi all a bit new to this so here goes

I am trying to achive the following quiries in sql

total cost of the cars sold by each salesman in the past month I can only get it to show all the sales in the db in month order

add the commission earned by each salesman to the above report

outputs both the most expensive and least expensive car sold in the past month

and finaly

all car sales in the past month. Include salesperson number, salesperson name, customer number, customer name and customer address

meany thanks

Mike
Dec 11 '06 #1
Share this Question
Share on Google+
3 Replies


markmcgookin
Expert 100+
P: 648
Hi all a bit new to this so here goes

I am trying to achive the following quiries in sql

total cost of the cars sold by each salesman in the past month I can only get it to show all the sales in the db in month order

add the commission earned by each salesman to the above report

outputs both the most expensive and least expensive car sold in the past month

and finaly

all car sales in the past month. Include salesperson number, salesperson name, customer number, customer name and customer address

meany thanks

Mike
Hi Mike,

Seems simple enough, but can you post more details?

i.e. information about the tables that will be used. e.g. tableName{Field1 (PK), Field 2, Field 3} etc

Mark
Dec 11 '06 #2

P: 5
Hi Mike,

Seems simple enough, but can you post more details?

i.e. information about the tables that will be used. e.g. tableName{Field1 (PK), Field 2, Field 3} etc

Mark
Hi ok here are my table entries

Customer
Create Table Customer(
CustomerNo COUNTER Primary Key NOT NULL,
FirstName VarChar(50) not null,
LastName VarChar(50) not null,
Address VarChar(30),
Town VarChar(30),
Postcode VarChar(9),
Telephone VarChar(12)
)

Centre
Create Table Centre(
CentreNo COUNTER Primary Key NOT NULL,
Name varchar(40),
Address VarChar(30),
PostCode VarChar(30),
Telephone VarChar (20)
)

Sales Staff
Create Table SalesStaff(
SalespersonNo COUNTER Primary Key NOT NULL,
SalespersonName VarChar(40) not null,
CentreNo int,
Foreign Key (CentreNo) REFERENCES Centre(CentreNo)
)


Car Sale
Create Table CarSale(
CarReg VarChar(10) Primary Key NOT NULL,
CarPrice VarChar(10),
CarModel VarChar(20),
CarMake VarChar(10),
SalespersonNo int,
CustomerNo int,
SaleDate Date,
Foreign Key (SalespersonNo) REFERENCES SalesStaff (SalespersonNo),
Foreign Key (CustomerNo) REFERENCES Customer(CustomerNo)
)

commission

Car Price() % Commission
0-5000 2
5000-10000 3
10000-15000 4
15000+ 5


and these are the queries I need to get from them

total cost of the cars sold by each salesman in the past month (For example: S1 has sold R123 JKN 6,500
X567 JMK 10,100
therefore the output would be S1 16,600)

add the commission earned by each salesman to the above report

outputs both the most expensive and least expensive car sold in the past month

and finally

all car sales in the past month. Include salesperson number, salesperson name, customer number, customer name and customer address in your output. Order by salesperson number.


thanks in advance

Mike
Dec 11 '06 #3

P: 5
Hi ok here are my table entries

Customer
Create Table Customer(
CustomerNo COUNTER Primary Key NOT NULL,
FirstName VarChar(50) not null,
LastName VarChar(50) not null,
Address VarChar(30),
Town VarChar(30),
Postcode VarChar(9),
Telephone VarChar(12)
)

Centre
Create Table Centre(
CentreNo COUNTER Primary Key NOT NULL,
Name varchar(40),
Address VarChar(30),
PostCode VarChar(30),
Telephone VarChar (20)
)

Sales Staff
Create Table SalesStaff(
SalespersonNo COUNTER Primary Key NOT NULL,
SalespersonName VarChar(40) not null,
CentreNo int,
Foreign Key (CentreNo) REFERENCES Centre(CentreNo)
)


Car Sale
Create Table CarSale(
CarReg VarChar(10) Primary Key NOT NULL,
CarPrice VarChar(10),
CarModel VarChar(20),
CarMake VarChar(10),
SalespersonNo int,
CustomerNo int,
SaleDate Date,
Foreign Key (SalespersonNo) REFERENCES SalesStaff (SalespersonNo),
Foreign Key (CustomerNo) REFERENCES Customer(CustomerNo)
)

commission

Car Price() % Commission
0-5000 2
5000-10000 3
10000-15000 4
15000+ 5


and these are the queries I need to get from them

total cost of the cars sold by each salesman in the past month (For example: S1 has sold R123 JKN 6,500
X567 JMK 10,100
therefore the output would be S1 16,600)

add the commission earned by each salesman to the above report

outputs both the most expensive and least expensive car sold in the past month

and finally

all car sales in the past month. Include salesperson number, salesperson name, customer number, customer name and customer address in your output. Order by salesperson number.


thanks in advance

Mike

I have tried
SELECT [CarSale].[SaleDate] AS [SaleDate By Month], CarSale.SalespersonNo, Sum(CarSale.CarPrice) AS [Sum Of CarPrice]
FROM CarSale
WHERE SaleDate >= 01/12/2006
GROUP BY [CarSale].[SaleDate], CarSale.SalespersonNo, [CarSale].[SaleDate], [CarSale].[SaleDate];

but it just lists all months not just the last one ?
Dec 11 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.