OraFAQ Forum: SQL & PL/SQL » SQL query help

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help

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

SQL query help [message #638282]

Mon, 08 June 2015 18:33

Developer9
Messages: 6
Registered: June 2015

Junior Member

Question: I am working on the following SQL query and need help with Oracle DB

DAILY_SALES table holds daily sales amount of each store and each customer; it has following 4 columns:
• STORE_NUMBER,CUSTOMER_NUMBER,TRANSACTION_DATE ,SALES_AMOUNT

SQL query to find out all stores that had more than 20000 dollar sales in the year of 2013?

Select * from DAILY_SALES where SALES_AMOUNT>20000 and TRANSACTION_DATE is in between (Jan1,2013 and Dec31,2013)

Report message to a moderator

Re: SQL query help [message #638283 is a reply to message #638282]

Mon, 08 June 2015 18:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

In theory, theory and practice are identical. In practice, theory and practice are unrelated.

BTW, we don't do homework assignments.

Report message to a moderator

Re: SQL query help [message #638284 is a reply to message #638282]

Mon, 08 June 2015 19:00

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Is this a college homework question? You need to look at aggregation: GROUP BY and SUM

Report message to a moderator

Re: SQL query help [message #638285 is a reply to message #638284]

Mon, 08 June 2015 19:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

 1* select deptno, sum(sal) from emp having sum(sal) > 9000 group by deptno
SQL> /

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875

Report message to a moderator

Re: SQL query help [message #638323 is a reply to message #638285]

Tue, 09 June 2015 08:27

abrarkiit
Messages: 5
Registered: May 2015
Location: India

Junior Member

Select * from DAILY_SALES where SALES_AMOUNT>20000 and to_char(TRANSACTION_DATE,'ýyyy')=2013;

hope this is useful. Developer9

Report message to a moderator

Re: SQL query help [message #638324 is a reply to message #638323]

Tue, 09 June 2015 08:51

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

Quote:

to_char(TRANSACTION_DATE,'ýyyy')=2013;

Interesting, you are extracting the YEAR from the date as a CHAR value, but then you perform an Implicit conversion to a NUMBER in your comparison to 2013. Implicit conversions are bad... You should either "Quote" your year ('2013') or better yet, make the YEAR a number.

TO_NUMBER(TO_CHAR(TRANSACTION_DATE,'ýyyy')) = 2013

Craig...

Report message to a moderator

Re: SQL query help [message #638326 is a reply to message #638324]

Tue, 09 June 2015 08:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or better use EXTRACT function:

SQL> select extract(year from sysdate) yr from dual;
        YR
----------
      2015

Report message to a moderator

Re: SQL query help [message #638347 is a reply to message #638326]

Tue, 09 June 2015 19:46

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Neither is better. any column expressions should be avoided:

TRANSACTION_DATE >= DATE '2013-01-01' and TRANSACTION_DATE < DATE '2014-01-01'

would use index on TRANSACTION_DATE if there is one and will save you time looking for all places where to_char(TRANSACTION_DATE,'yyyy') or extract(year from sysdate) (or any other expression against TRANSACTION_DATE is used when you will decide to index TRANSACTION_DATE.

SY.

Report message to a moderator

Re: SQL query help [message #638354 is a reply to message #638347]

Wed, 10 June 2015 00:50

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Unless you index the expression...

Report message to a moderator

Previous Topic:	cumulative sum
Next Topic:	What we should use for better function or procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 11:17:02 CDT 2024