Home » SQL & PL/SQL » SQL & PL/SQL » Can't qrouping or sorting in monthwise (merged 2)
Can't qrouping or sorting in monthwise (merged 2) [message #405290] Wed, 27 May 2009 05:39 Go to next message
rhnilu
Messages: 12
Registered: January 2009
Location: Bangladesh
Junior Member
How can I query in monthwise (grouping)
and sorting by in monthwise (order by)

In this query give me result in date wise, not month wise:

SELECT Agent_id, TO_DATE(Sdate,'mon-rr') month,
sum(Debit), sum(Credit)
FROM ad_order
group by agent_id, month

But this query give result in month wise but can't sorting in month wise:

SELECT Agent_id, to_char(Sdate,'mon-rr') month,
sum(Debit), sum(Credit)
FROM ad_order
group by agent_id, month

please help me.
Re: Can't qrouping or sorting in monthwise [message #405297 is a reply to message #405290] Wed, 27 May 2009 05:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
1. Is SDATE already date field? If yes then why are you doing to_date on date field and if sdate is a varchar then you have bad table design which will cause data quality and integrity issue and many other such headache down the line.

2. To have result set ordered you need to use Order by.

And finally

3. If you thought group by orders the result set then you need to read this http://tkyte.blogspot.com/search?q=order+in+the+court

[Updated on: Wed, 27 May 2009 05:57]

Report message to a moderator

Re: Can't qrouping or sorting in monthwise [message #405305 is a reply to message #405290] Wed, 27 May 2009 06:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want the results to come out in a specific order, you need an ORDER BY clause.

No Order By = No order.
Re: Can't qrouping or sorting in monthwise [message #405306 is a reply to message #405290] Wed, 27 May 2009 06:03 Go to previous messageGo to next message
rhnilu
Messages: 12
Registered: January 2009
Location: Bangladesh
Junior Member
sdate is date datatype

I used to_date for month wise result
Re: Can't qrouping or sorting in monthwise [message #405307 is a reply to message #405290] Wed, 27 May 2009 06:06 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Have a look at the trunc function.
http://www.techonthenet.com/oracle/functions/trunc_date.php

But which datatype is this sdate column?? In the first query you use to_date on it, and in the second you use to_char.. If it's a date then to_date is invalid. If it's a text string then to_char is invalid.
Re: Can't qrouping or sorting in monthwise [message #405309 is a reply to message #405290] Wed, 27 May 2009 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
rhnilu wrote on Wed, 27 May 2009 11:45
In this query give me result in date wise, not month wise:

SELECT Agent_id, TO_DATE(Sdate,'mon-rr') month,
sum(Debit), sum(Credit)
FROM ad_order
group by agent_id, month

But this query give result in month wise but can't sorting in month wise:

SELECT Agent_id, to_char(Sdate,'mon-rr') month,
sum(Debit), sum(Credit)
FROM ad_order
group by agent_id, month




Really? Since you can't reference aliases in a group by I reckon both of those give ORA-00904: invalid identifier.

Quote:

sdate is date datatype

I used to_date for month wise result



You'd need to use to_char or trunc to do that. to_dateing a date is ALWAYS wrong.

Suggest you read up on to_date, to_char and date formats in the oracle documentation because you don't understand them.

Clue - DATE datatype always has year/month/day and time components.
Re: Can't qrouping or sorting in monthwise [message #405310 is a reply to message #405290] Wed, 27 May 2009 06:10 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You cannot use select alias in group clause (here "month")
2/ If you need to order by month then add it to select, group and order by clauses.

Regards
Michel
Previous Topic: trigger problem
Next Topic: How to pass a value from one procedure to another block by calling it
Goto Forum:
  


Current Time: Sun Dec 04 08:41:32 CST 2016

Total time taken to generate the page: 0.06258 seconds