Trying to form these Queries

[mike2098]

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

[markmcgookin]

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

[mike2098]

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

[mike2098]

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 ?