| Query Help avg and count [message #234686] |
Wed, 02 May 2007 20:51  |
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
|
Member |
|
|
Hi guys I need to write a query to find the average no. of item borrowed for a customer on 14-03-2007
In the table im using there is a loandates column which contains the date im trying to use.
Im using oracle 10 and sqlplus
I know this doesnt compile but I had tried something along these lines:
select count(*) from transactions as penguin where loadndate = '14-03-2007' select avg(penguin);
Any help would be greatly appreciated.
|
|
|
|
| Re: Query Help avg and count [message #234688 is a reply to message #234686] |
Wed, 02 May 2007 21:33  |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
Not giving the relevant columns of the table makes it difficult to formulate the query. Nevertheless, this is what I think you are attempting to do:
select avg(count(borrowings_from_customers)) as borrowings
from transactions
group by borrowings_from_customers having loandate
= to_date('14/03/2007,'dd/mm/yyyy'));
Note: borrowings_from_customers is an assumed column. You will have to substitute the real column name for it. Hope this helps.
|
|
|
|
| Re: Query Help avg and count [message #234690 is a reply to message #234688] |
Wed, 02 May 2007 21:53 |
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
|
Member |
|
|
My apologies I forgot to upload the table.
CREATE TABLE Transactions (TransNo number (12), PurchaseID varchar (12), CID Varchar(12), LoanDate varchar (10), StaffName char (20), ReturnDate varchar (10),
CONSTRAINT PK_TRANSNO PRIMARY KEY (TransNo),
CONSTRAINT FK_Items_CODE FOREIGN KEY (PurchaseID) REFERENCES Item (Code),
CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CID) REFERENCES Customers (ID),
CONSTRAINT FK_TRANSACTIONS_NAME FOREIGN KEY (StaffName) REFERENCES Staff (Name));
|
|
|
|
| Re: Query Help avg and count [message #234695 is a reply to message #234690] |
Wed, 02 May 2007 22:54 |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
The table that you have given seems a straightforward transaction table. However, before I attempt to answer your question again, I would like to seek a clarification regarding the question asked.
You have written: 'need to write a query to find the average no. of item borrowed for a customer on 14-03-2007'
You will have to clarify the scenario. Normally, the customer buys from you either on cash or credit. You don't normally borrow for a customer. Can you elaborate on that?
|
|
|
|
|
|
|
|
|
|
| Re: Query Help avg and count [message #234845 is a reply to message #234837] |
Thu, 03 May 2007 05:34 |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
I was not offering a fool-proof solution in the absence of relevant information. My answer was supposed to be only of an indicative nature. Tell me which column in your table tells me about customer borrowings. I can't find any. Yet, I am supposed to calculate customer borrowings on a particular date. That is, pretty ridiculous, to say the least.
Further, the solution I offered was not averaging the customer_id as you said, instead it was showing the average number of times a customer_id showed up on that date.
|
|
|
|
| Re: Query Help avg and count [message #234847 is a reply to message #234845] |
Thu, 03 May 2007 05:38 |
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
|
Member |
|
|
loan date tells you when the item was borrowed ie the date which is what is needed to calculate the average number of items borrowed on that day. The item code tells you the number of the item.
Also from what I understand the only value needed to calculate it is the date.
|
|
|
|
| Re: Query Help avg and count [message #234868 is a reply to message #234847] |
Thu, 03 May 2007 06:35 |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
| Quote: | Hi guys I need to write a query to find the average no. of item borrowed for a customer on 14-03-2007
|
Even if we for a moment ignore the grammatical error in the above sentence, the import of it is quite clear. You want to know the average no. of items borrowed by a customer on a particular date. And that cannot be solved using loandate and itemid. Period.
On the other hand, if you really wanted a query that shows the average of the number of times an item was borrowed on a particular date, then while that can easily be solved using loandate and itemid, I must say that is not easily discernible by reading your question. Is this what you want?
select avg(count(itemid)) from transactions
group by itemid; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
