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

ORDER BY (SALES divided by ORDERS) ASC

BostonLegal
P: 9
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!
Feb 10 '08 #1
Share this Question
Share on Google+
16 Replies


Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Customers.CustomerID, Count(Customers.CustomerID) AS Orders, Sum(Customers.Sales) AS [Total Sales], round(Sum([Sales])/[Orders]) AS [Rounded Average Sales]
  3. FROM Customers
  4. GROUP BY Customers.CustomerID
  5. ORDER BY Sum(Customers.Sales) DESC, Count(Customers.CustomerID) DESC;
  6.  
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,TotalSales and Average columns being calculated from that data.

Regards

Jim :)
Feb 10 '08 #2

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSql As String
  2.  
  3. strSql="SELECT SALES, ORDERS, SALES/ORDERS AS AVG_SPENT FROM CUSTOMERS ORDER BY AVG_SPENT ASC"
Feb 10 '08 #3

BostonLegal
P: 9
Try this and see if it helps:
Expand|Select|Wrap|Line Numbers
  1. Dim strSql As String
  2.  
  3. 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. :(
Feb 10 '08 #4

BostonLegal
P: 9
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Customers.CustomerID, Count(Customers.CustomerID) AS Orders, Sum(Customers.Sales) AS [Total Sales], round(Sum([Sales])/[Orders]) AS [Rounded Average Sales]
  3. FROM Customers
  4. GROUP BY Customers.CustomerID
  5. ORDER BY Sum(Customers.Sales) DESC, Count(Customers.CustomerID) DESC;
  6.  
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,TotalSales 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. :)
Feb 10 '08 #5

BostonLegal
P: 9
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".

Expand|Select|Wrap|Line Numbers
  1.  if request("avg_spent")<>"" then
  2. sql="SELECT [name],[status],[deal],[sales],[sales]/[deal] AS [avg] 
  3. FROM [Customer] WHERE [deal] <> 0 ORDER BY [sales]/[deal] "&request("avg_spent")&""
  4. 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$
Feb 11 '08 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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;
Feb 11 '08 #7

BostonLegal
P: 9
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?
Expand|Select|Wrap|Line Numbers
  1. SELECT [truename],[status],[deal],[sales],[sales]/[deal] AS [avg]  FROM [Customer] WHERE [deal] <> 0 ORDER BY [sales]/[deal] ASC
Feb 11 '08 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry I misread sales/orders as a field name.

Expand|Select|Wrap|Line Numbers
  1. SELECT [truename],[status],[deal],[sales],[sales]/nz([deal],1) AS [avg] 
  2. FROM [Customer] 
  3. ORDER BY [sales]/[deal] ASC
  4.  
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.
Feb 11 '08 #9

BostonLegal
P: 9
Sorry I misread sales/orders as a field name.

Expand|Select|Wrap|Line Numbers
  1. SELECT [truename],[status],[deal],[sales],[sales]/nz([deal],1) AS [avg] 
  2. FROM [Customer] 
  3. ORDER BY [sales]/[deal] ASC
  4.  
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [truename],[deal],[sales],[sales]/nz([deal],1) AS [avg] 
  2. FROM [Customer]
  3.  
Feb 11 '08 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry all the fields should have spaces between them, try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer] 
  2. ORDER BY [sales]/[deal] ASC
  3.  
Feb 11 '08 #11

BostonLegal
P: 9
Sorry all the fields should have spaces between them, try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer] 
  2. ORDER BY [sales]/[deal] ASC
  3.  
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.

Expand|Select|Wrap|Line Numbers
  1. sql="SELECT [truename],[status],[deal],[sales],[sales]/[deal] FROM [Customer] WHERE [deal] <> 0 ORDER BY [sales]/[deal] "&request("avg_spent")&"" 
  2. sql=sql&" UNION"
  3. sql=sql&" SELECT [truename],[status],[deal],[sales],[sales] FROM [Customer] WHERE [deal] = 0 ORDER BY [sales] "&request("avg_spent")&"" 
  4.  
Thanks a lot to you for your help and suggestions. The forum is too good. ;)
Feb 11 '08 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
OK try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer] 
  2. ORDER BY [sales]/nz([deal],1) ASC
  3.  
Feb 11 '08 #13

BostonLegal
P: 9
OK try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer] 
  2. ORDER BY [sales]/nz([deal],1) ASC
  3.  
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.
Feb 11 '08 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [truename], [STATUS], [deal], [sales], 
  2. IIf([deal]=0,[Sales],[Sales]/[Deal]) AS [avg] 
  3. FROM [Customer]
  4. ORDER BY IIf([deal]=0,[Sales],[Sales]/[Deal]) ASC
  5.  
Feb 11 '08 #15

BostonLegal
P: 9
OK then, lets do it the long way around...try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT [truename], [STATUS], [deal], [sales], 
  2. IIf([deal]=0,[Sales],[Sales]/[Deal]) AS [avg] 
  3. FROM [Customer]
  4. ORDER BY IIf([deal]=0,[Sales],[Sales]/[Deal]) ASC
  5.  
MIRACLE!!!! It works perfect this time.
Thanks a lot pal, you really refused to let it be.
Let me know if ever you're flying over to shanghai! ;)
Feb 11 '08 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
MIRACLE!!!! It works perfect this time.
Thanks a lot pal, you really refused to let it be.
Let me know if ever you're flying over to shanghai! ;)
:)

Glad its working for you.
Feb 11 '08 #17

Post your reply

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