OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Select query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select query [message #269956]

Tue, 25 September 2007 04:21

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

Report message to a moderator

Re: Select query [message #269960 is a reply to message #269956]

Tue, 25 September 2007 04:33

Frank
Messages: 7901
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!

Report message to a moderator

Re: Select query [message #269978 is a reply to message #269956]

Tue, 25 September 2007 05:07

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

Report message to a moderator

Re: Select query [message #269988 is a reply to message #269978]

Tue, 25 September 2007 05:43

Frank
Messages: 7901
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?

Report message to a moderator

Re: Select query [message #269995 is a reply to message #269978]

Tue, 25 September 2007 06:16

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

sum(trade_quanty)/count(trade_quanty) avg_trade

Don't you know there is an AVG function?

Regards
Michel

Report message to a moderator

Re: Select query [message #270012 is a reply to message #269995]

Tue, 25 September 2007 07:27

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

Report message to a moderator

Re: Select query [message #270014 is a reply to message #269956]

Tue, 25 September 2007 07:29

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.

Report message to a moderator

Re: Select query [message #270015 is a reply to message #270014]

Tue, 25 September 2007 07:37

Littlefoot
Messages: 21823
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

joy_division
Messages: 4963
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

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 10 02:16:27 CST 2024