Home » SQL & PL/SQL » SQL & PL/SQL » Select query
Select query [message #269956] Tue, 25 September 2007 04:21 Go to next message
moinul
Messages: 1
Registered: September 2007
Location: Dhaka
Junior Member

Hi all

Can anyone help me to find weekly average trade_qty from the following fields

trade_date trade_qty
01092007 10200
02092007 10500
03092007 10660
04092007 17700
05092007 15400

Trade_date is date field. An SQL query would be fine.

Thanks in advance
Re: Select query [message #269960 is a reply to message #269956] Tue, 25 September 2007 04:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi, welcome to the forum

In this forum we have some rules, which you can find by following the link in the yellow bar on top of the forum-list.
One of the rules is that you should show us that you tried before asking; we are not a homework-solutions-generator.

So: show us your query and the outcome (using code tags as described in the link I just mentioned) and why you think it is not good.
Add to that the desired output and I guarantee you a fast help will be yours!
Re: Select query [message #269978 is a reply to message #269956] Tue, 25 September 2007 05:07 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Try with this
create table trade_info (trade_date date, trade_quanty number);

insert into trade_info values('01-sep-2007',10200);

insert into trade_info values('02-sep-2007',10500);

insert into trade_info values('03-sep-2007',10660);

insert into trade_info values('04-sep-2007',17700);

insert into trade_info values('05-sep-2007',15400);

select to_char(trade_date,'yyyy') year, 
to_char(trade_date,'WW') weak, 
sum(trade_quanty) total_trade_amt,
count(trade_quanty) no_of_trade,
sum(trade_quanty)/count(trade_quanty) avg_trade
from trade_info
group by to_char(trade_date,'yyyy'),to_char(trade_date,'WW')

YEAR WEEK  TOTAL_TRADE_AMT NO_OF_TRADE  AVG_TRADE
---- ----  -------------   -----------  ----------
2007 36           43760           3     14586.6667
2007 35           20700           2     10350
Re: Select query [message #269988 is a reply to message #269978] Tue, 25 September 2007 05:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> create table trade_info (trade_date date, trade_quanty number);

Table created.

SQL> insert into trade_info values('01-sep-2007',10200);
insert into trade_info values('01-sep-2007',10200)
                              *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

'01-sep-2007' is not a date. it is a string.

Did you read my reply? Did you understand the homework-part?
Re: Select query [message #269995 is a reply to message #269978] Tue, 25 September 2007 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
sum(trade_quanty)/count(trade_quanty) avg_trade

Don't you know there is an AVG function?

Regards
Michel
Re: Select query [message #270012 is a reply to message #269995] Tue, 25 September 2007 07:27 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Michel Cadot wrote on Tue, 25 September 2007 06:16

Don't you know there is an AVG function?


Sorry i know it, But forget to use it. Thanks michel

Re: Select query [message #270014 is a reply to message #269956] Tue, 25 September 2007 07:29 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

SQL> insert into trade_info values('01-sep-2007',10200);
insert into trade_info values('01-sep-2007',10200)
                              *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected




But i can insert it.
SQL> insert into trade_info values('01-sep-2007',10200);

1 row created.


Re: Select query [message #270015 is a reply to message #270014] Tue, 25 September 2007 07:37 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Interesting ... I can not:
SQL> insert into trade_info values('01-sep-2007',10200);
insert into trade_info values('01-sep-2007',10200)
                              *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
The fact that YOU can insert it doesn't mean that everyone else may. It is a matter of NLS settings which made it possible. If they were different, you'd suffer from the same error as well. Always take care about explicit datatype conversion!

Try to do something like this and see for yourself:
SQL> alter session set nls_date_format = 'mm dd yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
09 25 2007

SQL> insert into trade_info values('01-sep-2007',10200);

[Updated on: Tue, 25 September 2007 07:37]

Report message to a moderator

Re: Select query [message #270022 is a reply to message #270014] Tue, 25 September 2007 07:56 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
[quote title=muzahidul islam wrote on Tue, 25 September 2007 08:29]
Quote:

But i can insert it.
SQL> insert into trade_info values('01-sep-2007',10200);

1 row created.





And we have told you many many times and you still don't understand what a DATE is. Is your brain full? Do you not know how to learn any new (or correct) concepts?
You think you are helping others, but when they try to run your incorrect answer and it doesn't work for them, they'll write you off as some quack. It might be time to read the Oracle Concepts Manual.

Here are just two of your postings where you show no signs of understanding what a DATE is:

http://www.orafaq.com/forum/m/267782/66800/#msg_267782
http://www.orafaq.com/forum/m/266254/66800/#msg_266254

[Updated on: Tue, 25 September 2007 08:00]

Report message to a moderator

Re: Select query [message #270139 is a reply to message #269956] Tue, 25 September 2007 22:07 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

The fact that YOU can insert it doesn't mean that everyone else may. It is a matter of NLS settings which made it possible. If they were different, you'd suffer from the same error as well. Always take care about explicit datatype conversion!



Thanks. From next time i will follow it.
SQL> alter session set nls_date_format = 'mm dd yyyy'
  2  /

Session altered.

SQL> insert into trade_info values('01-sep-2007',10200);
insert into trade_info values('01-sep-2007',10200)
                              *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> insert into trade_info values(to_date('01-sep-2007','dd-mon-yyyy'),10200);

1 row created.

Previous Topic: Initialize a CLOB Locator?
Next Topic: Date Comparison using to_date
Goto Forum:
  


Current Time: Sun Dec 04 14:26:01 CST 2016

Total time taken to generate the page: 0.07669 seconds