Home » SQL & PL/SQL » SQL & PL/SQL » Group by error on ytd
Group by error on ytd [message #243836] Sat, 09 June 2007 14:00 Go to next message
oraslearn
Messages: 1
Registered: June 2007
Junior Member
I'm trying to do a count of product names as a YTD looking for this kind of an output.
bat 3

ball 1
baseball 1

SQL> select product_name,start_date from inventory;

PRODUCT_NAME START_DAT
-------------------------------------------------- ---------
bat 15-FEB-07
bat 15-APR-07
ball 15-MAY-07
baseball 15-JUN-07
bat 02-JUN-07

SQL>
select product_name,
sum(decode(trunc(START_DATE,'MON'), add_months(trunc(sysdate,'MON'), 0), count(product_name), 0
)),
sum(count(product_name))
from inventory
group by product_name;

select product_name,
*
ERROR at line 1:
ORA-00937: not a single-group group function

i fixed the error by taking out product_name from select but i need product_name also included.Can some one help?
Re: Group by error on ytd [message #243840 is a reply to message #243836] Sat, 09 June 2007 16:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your Oracle version (4 decimals).
Please read and apply How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS.

What does mean YTD?

Btw, "add_months(trunc(sysdate,'MON'), 0)" is just "trunc(sysdate,'MON')". Why using add_months if it is just to add 0?

Regards
Michel

[Updated on: Sat, 09 June 2007 16:10]

Report message to a moderator

Re: Group by error on ytd [message #243842 is a reply to message #243836] Sat, 09 June 2007 17:06 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
YTD is an acronym for Year To Date
http://acronymfinder.com
Re: Group by error on ytd [message #243846 is a reply to message #243842] Sun, 10 June 2007 00:27 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, thanks. I hate acronyms in forum but forum acronyms. Razz

select product_name,
       count(*) overall_count,
       count(case 
             when extract(year from start_date) = extract(year from sysdate)
             then 1
             end) year_count
from inventory
group by product_name
/

Regards
Michel
Previous Topic: SQL performance tuning
Next Topic: about lockings in database
Goto Forum:
  


Current Time: Sat Dec 03 12:13:46 CST 2016

Total time taken to generate the page: 0.11217 seconds