Home » SQL & PL/SQL » SQL & PL/SQL » Query Help avg and count
Query Help avg and count [message #234686] Wed, 02 May 2007 20:51 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #234696 is a reply to message #234695] Wed, 02 May 2007 22:57 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi its an assignment for school.

Thats the questions that we were given. There is no more detail sorry.
Re: Query Help avg and count [message #234705 is a reply to message #234696] Thu, 03 May 2007 00:10 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Try something like this:
Select avg(count(CID))
from transactions
where loandate=to_date('14/03/2007','dd/mm/yyyy')
group by CID;
Re: Query Help avg and count [message #234837 is a reply to message #234686] Thu, 03 May 2007 05:04 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi,

Then it averages the id number not how many items were borrowed on a certain day.
Re: Query Help avg and count [message #234845 is a reply to message #234837] Thu, 03 May 2007 05:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: Query Help avg and count [message #234911 is a reply to message #234690] Thu, 03 May 2007 07:57 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I see you defined your DATE columns as VARCHAR2. Bad idea.
Previous Topic: Auditing
Next Topic: How to search a string value irrespective of case sensitive
Goto Forum:
  


Current Time: Sun Dec 04 08:35:51 CST 2016

Total time taken to generate the page: 0.10034 seconds