434,776 Members | 1,302 Online
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

 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
16 Replies

 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   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,TotalSales and Average columns being calculated from that data. Regards Jim :) Feb 10 '08 #2

 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 Dim strSql As String   strSql="SELECT SALES, ORDERS, SALES/ORDERS AS AVG_SPENT FROM CUSTOMERS ORDER BY AVG_SPENT ASC" Feb 10 '08 #3

 P: 9 Try this and see if it helps: Expand|Select|Wrap|Line Numbers 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. :( Feb 10 '08 #4

 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   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,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

 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  if request("avg_spent")<>"" then sql="SELECT [name],[status],[deal],[sales],[sales]/[deal] AS [avg]  FROM [Customer] WHERE [deal] <> 0 ORDER BY [sales]/[deal] "&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\$ Feb 11 '08 #6

 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

 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 SELECT [truename],[status],[deal],[sales],[sales]/[deal] AS [avg]  FROM [Customer] WHERE [deal] <> 0 ORDER BY [sales]/[deal] ASC Feb 11 '08 #8

 Expert Mod 10K+ P: 14,534 Sorry I misread sales/orders as a field name. Expand|Select|Wrap|Line Numbers SELECT [truename],[status],[deal],[sales],[sales]/nz([deal],1) AS [avg]  FROM [Customer]  ORDER BY [sales]/[deal] ASC   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

 P: 9 Sorry I misread sales/orders as a field name. Expand|Select|Wrap|Line Numbers SELECT [truename],[status],[deal],[sales],[sales]/nz([deal],1) AS [avg]  FROM [Customer]  ORDER BY [sales]/[deal] ASC   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 SELECT [truename],[deal],[sales],[sales]/nz([deal],1) AS [avg]  FROM [Customer]   Feb 11 '08 #10

 Expert Mod 10K+ P: 14,534 Sorry all the fields should have spaces between them, try this ... Expand|Select|Wrap|Line Numbers SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer]  ORDER BY [sales]/[deal] ASC   Feb 11 '08 #11

 P: 9 Sorry all the fields should have spaces between them, try this ... Expand|Select|Wrap|Line Numbers SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer]  ORDER BY [sales]/[deal] ASC   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 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")&""    Thanks a lot to you for your help and suggestions. The forum is too good. ;) Feb 11 '08 #12

 Expert Mod 10K+ P: 14,534 OK try this ... Expand|Select|Wrap|Line Numbers SELECT [truename], [status], [deal], [sales], [sales]/nz([deal],1) AS [avg] FROM [Customer]  ORDER BY [sales]/nz([deal],1) ASC   Feb 11 '08 #13

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

 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 SELECT [truename], [STATUS], [deal], [sales],  IIf([deal]=0,[Sales],[Sales]/[Deal]) AS [avg]  FROM [Customer] ORDER BY IIf([deal]=0,[Sales],[Sales]/[Deal]) ASC   Feb 11 '08 #15

 P: 9 OK then, lets do it the long way around...try this: Expand|Select|Wrap|Line Numbers SELECT [truename], [STATUS], [deal], [sales],  IIf([deal]=0,[Sales],[Sales]/[Deal]) AS [avg]  FROM [Customer] ORDER BY IIf([deal]=0,[Sales],[Sales]/[Deal]) ASC   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

 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