ORDER BY (SALES divided by ORDERS) ASC

[BostonLegal]

Hello,

I'm selling sandwiches online and I'm trying to create a asp page where I can review my sales and found out who is my best customers and so on...

My table (CUSTOMERS) lists the number of times a same customer ordered (ORDERS) and the total amount of money he spent (SALES).

Now, I'd like to calculate my customer's average spending : SALES divided by ORDERS.

sql="SELECT * FROM CUSTOMERS ORDER BY SALES/ORDERS ASC"
This is my sql statement and of course, it doesn't work, please help me.

- I prefer not adding another column in my database cause it's already huge enough.
- I need to calculate the average in the sql so that I can order my results ascending or descending.

Thank you!

[Jim Doherty]

Hello,

I'm selling sandwiches online and I'm trying to create a asp page where I can review my sales and found out who is my best customers and so on...

My table (CUSTOMERS) lists the number of times a same customer ordered (ORDERS) and the total amount of money he spent (SALES).

Now, I'd like to calculate my customer's average spending : SALES divided by ORDERS.

sql="SELECT * FROM CUSTOMERS ORDER BY SALES/ORDERS ASC"
This is my sql statement and of course, it doesn't work, please help me.

- I prefer not adding another column in my database cause it's already huge enough.
- I need to calculate the average in the sql so that I can order my results ascending or descending.

Thank you!

Assumptions

Table having fields
CustomerID..... .... datatype....... . Number
Sales.......... .......... datatype....... Currency

Working off the principle individual sale amount per customer are listed in the table called customers along with their customer ID... then the following SQL will display their customer id, the total count of that customer id (which maps out therefore as an order total) then the total sum of each individual sale item and lastly the rounded average of your sales total divided by the total order (customer Id count)

Obviously if this doesnt resemble your particular table then I'm sure you can amend to suit.

 
SELECT Customers.CustomerID, Count(Customers.CustomerID) AS Orders, Sum(Customers.Sales) AS [Total Sales], round(Sum([Sales])/[Orders]) AS [Rounded Average Sales]
FROM Customers
GROUP BY Customers.CustomerID
ORDER BY Sum(Customers.Sales) DESC, Count(Customers.CustomerID) DESC;

You can order by as you like for the relevant columns as you wish by amending the usual ORDER BY clause to your liking this merely orders by the total orders first then sum of sales as per your post

You will see a four column dataset returned for a two mere two column physical data input (CustomerID and Sale amount). Orders,TotalSal es and Average columns being calculated from that data.

Regards

Jim :)

[puppydogbuddy]

Hello,

I'm selling sandwiches online and I'm trying to create a asp page where I can review my sales and found out who is my best customers and so on...

My table (CUSTOMERS) lists the number of times a same customer ordered (ORDERS) and the total amount of money he spent (SALES).

Now, I'd like to calculate my customer's average spending : SALES divided by ORDERS.

sql="SELECT * FROM CUSTOMERS ORDER BY SALES/ORDERS ASC"
This is my sql statement and of course, it doesn't work, please help me.

- I prefer not adding another column in my database cause it's already huge enough.
- I need to calculate the average in the sql so that I can order my results ascending or descending.

Thank you!

Try this and see if it helps:

Dim strSql As String

strSql="SELECT SALES, ORDERS, SALES/ORDERS AS AVG_SPENT FROM CUSTOMERS ORDER BY AVG_SPENT ASC"

[BostonLegal]

Try this and see if it helps:

Dim strSql As String

strSql="SELECT SALES, ORDERS, SALES/ORDERS AS AVG_SPENT FROM CUSTOMERS ORDER BY AVG_SPENT ASC"

SELECT customer, company, status, orders, sales, sales/orders AS avg_spent FROM Customers ORDER BY avg_spent ASC

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.


Thanks a lot for your help!!! I just tried the query but it seems that my ACCESS database doesn't support it. :(

[BostonLegal]

Assumptions

Table having fields
CustomerID..... .... datatype....... . Number
Sales.......... .......... datatype....... Currency

Working off the principle individual sale amount per customer are listed in the table called customers along with their customer ID... then the following SQL will display their customer id, the total count of that customer id (which maps out therefore as an order total) then the total sum of each individual sale item and lastly the rounded average of your sales total divided by the total order (customer Id count)

Obviously if this doesnt resemble your particular table then I'm sure you can amend to suit.

 
SELECT Customers.CustomerID, Count(Customers.CustomerID) AS Orders, Sum(Customers.Sales) AS [Total Sales], round(Sum([Sales])/[Orders]) AS [Rounded Average Sales]
FROM Customers
GROUP BY Customers.CustomerID
ORDER BY Sum(Customers.Sales) DESC, Count(Customers.CustomerID) DESC;

You can order by as you like for the relevant columns as you wish by amending the usual ORDER BY clause to your liking this merely orders by the total orders first then sum of sales as per your post

You will see a four column dataset returned for a two mere two column physical data input (CustomerID and Sale amount). Orders,TotalSal es and Average columns being calculated from that data.

Regards

Jim :)

Thanks a lot Jim, I'll study your message and give it a try tonight.
I'll let you know how it went. :)

[BostonLegal]

SELECT customer, company, status, orders, sales, sales/orders AS avg_spent FROM Customers ORDER BY avg_spent ASC

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.


Thanks a lot for your help!!! I just tried the query but it seems that my ACCESS database doesn't support it. :(

In this code, avg_spent holds "ASC" or "DESC".

 if request("avg_spent")<>"" then
sql="SELECT [name],[status],[deal],[sales],[sales]/[deal] AS [avg] 
FROM [Customer] [B]WHERE [deal] <> 0[/B] ORDER BY [B][sales]/[deal][/B] "&request("avg_spent")&""
end if

Above is my current code.
- I had to add the WHERE condition to prevent the /0 when a customer made no orders.
- Also in the ORDER BY, I had to use [sales]/[deal] instead of [avg].

I still have several questions:
1. What are these "[" "]" used for? I never used any in my sql until now.
2. Is there a way to avoid using the WHERE condition?
3. I'd like to list those customers that have (0) sales / (0) deals but I exclude them in my where condition. :(

Example:
Name Status Deal Sales Avg
Paul 1 3 45$ 15$
John 1 1 10$ 10$
Carla 1 0 0$ 0$

[MMcCarthy]

SELECT customer, company, status, orders, sales, sales/orders AS avg_spent FROM Customers ORDER BY avg_spent ASC

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.


Thanks a lot for your help!!! I just tried the query but it seems that my ACCESS database doesn't support it. :(

Try this and see if it makes a difference...

SELECT customer, company, status, orders, sales, [sales/orders] AS [avg_spent] FROM Customers ORDER BY [avg_spent] ASC;

[BostonLegal]

Try this and see if it makes a difference...

SELECT customer, company, status, orders, sales, [sales/orders] AS [avg_spent] FROM Customers ORDER BY [avg_spent] ASC;

Thx but orders is sometimes = 0. so I can't simply do [sales/orders].

The following SELECT works good but I'd like to find a way to show customers who ordered nothing as well. How could I do that?

SELECT [truename],[status],[deal],[sales],[sales]/[deal] AS [avg]  FROM [Customer] WHERE [deal] <> 0 ORDER BY [sales]/[deal] ASC

[MMcCarthy]

Sorry I misread sales/orders as a field name.

[CODE=sql]
SELECT [truename],[status],[deal],[sales],[sales]/nz([deal],1) AS [avg]
FROM [Customer]
ORDER BY [sales]/[deal] ASC
[/CODE]

This will replace [deal]=0 with 1 as you can't divide by 0. However if will return the value in sales. If that is not what you want let me know.

[BostonLegal]

Sorry I misread sales/orders as a field name.

[CODE=sql]
SELECT [truename],[status],[deal],[sales],[sales]/nz([deal],1) AS [avg]
FROM [Customer]
ORDER BY [sales]/[deal] ASC
[/CODE]

This will replace [deal]=0 with 1 as you can't divide by 0. However if will return the value in sales. If that is not what you want let me know.

It's exactly what I want but I got an "ODBC driver does not support the requested properties". :'(

I removed the ORDER BY and tried the new SELECT below but it didn't help much; I got the same error message:
[CODE=sql]
SELECT [truename],[deal],[sales],[sales]/nz([deal],1) AS [avg]
FROM [Customer]
[/CODE]

[MMcCarthy]

Sorry all the fields should have spaces between them, try this ...

[CODE=sql]
SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer]
ORDER BY [sales]/[deal] ASC
[/CODE]

[BostonLegal]

Sorry all the fields should have spaces between them, try this ...

[CODE=sql]
SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer]
ORDER BY [sales]/[deal] ASC
[/CODE]

Same ERROR Message, properties not supported.
Please take a look at the following SQL.
I decided to go for a UNION of the two SQL statements (case deal = 0 and case >< 0).
Strangly enough, note that it works fine even I removed the AS [avg]!!!!
I removed it since anyway, I can't order on it but had to go through the [sales]/[deal]... Really wierd.

[CODE=sql]
sql="SELECT [truename],[status],[deal],[sales],[sales]/[deal] FROM [Customer] WHERE [deal] <> 0 ORDER BY [sales]/[deal] "&request("avg_ spent")&""
sql=sql&" UNION"
sql=sql&" SELECT [truename],[status],[deal],[sales],[sales] FROM [Customer] WHERE [deal] = 0 ORDER BY [sales] "&request("avg_ spent")&""
[/CODE]

Thanks a lot to you for your help and suggestions. The forum is too good. ;)

[MMcCarthy]

OK try this ...

[CODE=sql]
SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer]
ORDER BY [sales]/nz([deal],1) ASC
[/CODE]

[BostonLegal]

OK try this ...

[CODE=sql]
SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer]
ORDER BY [sales]/nz([deal],1) ASC
[/CODE]

It doesn't work for me. I don't know why.

I'll try it directly in Access software later but via my ASP, the ODBC proterties "nz([deal],1)" is not recognized. The same for "AS [avg]".

The UNION works fine for me on the server, I'm gonna stay with it until I find out what's wrong with my odbc on the server.

Sorry to trouble you when most probably your SQL is fine. :)
And thanks again for all.

[MMcCarthy]

It doesn't work for me. I don't know why.

I'll try it directly in Access software later but via my ASP, the ODBC proterties "nz([deal],1)" is not recognized. The same for "AS [avg]".

The UNION works fine for me on the server, I'm gonna stay with it until I find out what's wrong with my odbc on the server.

Sorry to trouble you when most probably your SQL is fine. :)
And thanks again for all.

OK then, lets do it the long way around...try this:
[code=sql]
SELECT [truename], [STATUS], [deal], [sales],
IIf([deal]=0,[Sales],[Sales]/[Deal]) AS [avg]
FROM [Customer]
ORDER BY IIf([deal]=0,[Sales],[Sales]/[Deal]) ASC
[/code]