|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 25th, 2004, 09:07 PM
|
||||
|
||||
|
Need help please
Can someone pleeease help me. I have a very simple problem but I can't seem to get around it.
 Okay here's the deal: I'm running SQL queries and returning their output using Oracle SQL* plus. Check out this statement:Code:
Select Count(*), Sales_Persons_ID, Order_Number
From Order
Group By Sales_Persons_ID
Having Count(*) >= All (Select Count(*)
From Order
Group By Sales_Persons_ID);
The SQL statement works fine without the "Order_Number" field in the first line. The output actually looks like this: Code:
COUNT(*) SALES_PERSONS_ID
--------- -----------------
18 183928727
However, when I include "Order_Number", I get a message that it's "not a group by expression." I know this means (or at least I think) that I have to include Order_Number in the Group By expression. But even when I do, I still get an error. You see, all I'm trying to do is pull that Order Number out along with the output. So instead of just getting a count of the order numbers, I get an actual list of order numbers. If anyone still needs more info to understand this, please let me know. Thanks
|
|
#2
May 26th, 2004, 06:17 PM
|
|||
|
|||
|
With Grouping, for each output, you what to specify what to do with it or group by it
Select Count(*), Sales_Persons_ID, Order_Number From Order Group By Sales_Persons_ID, Order_Number Having Count(*) >= All (Select Count(*) From Order Group By Sales_Persons_ID); OR Select Count(*), Sales_Persons_ID, First(Order_Number) From Order Group By Sales_Persons_ID Having Count(*) >= All (Select Count(*) From Order Group By Sales_Persons_ID); Don't know if "First" exists in Oracle, but you should get the point S-
|
|
#3
May 27th, 2004, 12:00 PM
|
||||
|
||||
|
I've tried the first statment before. I just get a message that "no rows selected." And the second statement won't even work. I get this message: "ERROR at line 1:
OCA-30021: error preparing/executing SQL statement [POL-5235] not a GROUP BY expression."
|
|
#4
May 28th, 2004, 02:25 AM
|
||||
|
||||
|
Hello. I just thought I would follow up on this problem since no one doesn't seem to be know. Another person on a different messageboard told me this: "Take the Order_Number from that outer select and use it in a
third select which lists all the orders for that sales person." I'm not sure how to do this but I hope this helps someone else to figure it out. Please...I'm pulling out my hair here!!  Lol...thanks.
|
|
#6
May 28th, 2004, 12:54 PM
|
||||
|
||||
|
Okay you see th output, well I get a count of the number orders there are. But what I really want instead is a list of the order numbers. So the list should give me a list of 13 order numbers along with (most likely) the sales person who made that order. Do you see what I mean?
|
|
#8
May 28th, 2004, 01:47 PM
|
||||
|
||||
|
Oh that tells me which sales person had the most sales or "orders" from out of all the sales persons. So the answer is sales person: 183928727 and with a total number of orders made: 18.
|
|
#10
May 28th, 2004, 03:25 PM
|
||||
|
||||
|
Quote:
Well no and yes. I can already get the total number of orders. All I want now is the list of the order numbers for that salesperson; as you said.
|
|
#11
May 28th, 2004, 03:35 PM
|
|||
|
|||
|
Code:
Select O.Sales_Persons_ID, (Select Count(*) From Order Where Order.Sales_Persons_ID = O.Sales_Persons_ID) as Count, O.Order_Number From Order as O Order By O.Sales_Persons_ID; Let me know if this works for you Syntax may be slightly off for Oracle, but this would work in SQL Server or MS Access. S-
|
|
#13
May 29th, 2004, 05:39 AM
|
||||
|
||||
|
Have hacked a workaround in access. Not sure of the syntax differences for Oracle (have never used it), bu the logic should be the same.
What I did was build two querries in the database. One to count the number of orders placed by each sales person, the second query to select the Top sales person from the count query and display their order numbers. I then tested output using ASP response.writes to make sure the correct data came out.
__________________
-
thought-after | my thoughts on web development Get Firefox, the developers browser Budget hosting - recommended [/left]
|
|
#14
May 29th, 2004, 05:56 AM
|
||||
|
||||
|
And so to the querries -
First query to count the number of orders placed by each sales person: Sales_Count Code:
SELECT Count(Order.Sales_Persons_ID) AS CountOfSales_Persons_ID, Order.Sales_Persons_ID FROM [Order] GROUP BY Order.Sales_Persons_ID ORDER BY Count(Order.Sales_Persons_ID) DESC; The second query to select the order numbers for the sales person with the highest number of orders: TopSales Code:
SELECT Sales_Count.CountOfSales_Persons_ID, Order.Order_Number, Sales_Count.Sales_Persons_ID FROM [Order] INNER JOIN Sales_Count ON Order.Sales_Persons_ID=Sales_Count.Sales_Persons_I D WHERE Sales_Count.CountOfSales_Persons_ID IN (SELECT TOP 1 Sales_Count.CountOfSales_Persons_ID FROM Sales_Count); It is the second query that can be used in ASP to return the dataset for writing to the page. The following code writes out the data from an Access database named Sale. Code:
<%
dim r 'Recordset object
dim sql : sql = "" 'SQL string
'create SQL query to show all order numbers for the sales person with the highest amount of sales
sql = "SELECT Sales_Count.CountOfSales_Persons_ID, Order.Order_Number, Sales_Count.Sales_Persons_ID"
sql = sql & " FROM [Order] INNER JOIN Sales_Count ON Order.Sales_Persons_ID = Sales_Count.Sales_Persons_ID"
sql = sql & " WHERE Sales_Count.CountOfSales_Persons_ID IN"
sql = sql & " (SELECT TOP 1 Sales_Count.CountOfSales_Persons_ID FROM Sales_Count)"
'create recordset object
Set r = Server.CreateObject("ADODB.Recordset")
r.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.mappath("sale.mdb")
'execute SQL statement
r.Open sql
Response.Write "Number of Sales: "
Response.Write r("CountOfSales_Persons_ID")
Response.Write "<br>"
Response.Write "Sales Rep: "
Response.Write r("Sales_Persons_ID")
Response.Write "<br>"
While Not r.EOF
Response.Write "Order Nr: "
Response.Write r("Order_Number")
Response.Write "<br>"
r.MoveNext
WEND
%>
As I said, the output is very rough, but it returns a single number for the number of sales made, the sales persons ID number, and a list of all Order Numbers for that sales person. Hope this helps and you can convert syntax for use in Oracle.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
