Home » SQL & PL/SQL » SQL & PL/SQL » Help in Pivot Query (Error ORA-00937: not a single-group group function) (Oracle, 8.1.7, Windows Server 2003)
Help in Pivot Query (Error ORA-00937: not a single-group group function) [message #331014] Wed, 02 July 2008 02:49 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear Experts,

I have the following Pivor Query when I run this getting
error ORA-00937: not a single-group group function. Can anyone help me?
SELECT DISTINCT svmd_code,
MAX (DECODE (TO_CHAR (ro_dtime, 'MON'), 'JAN', COUNT (ro_no))
) "JAN",
MAX (DECODE (TO_CHAR (ro_dtime, 'MON'), 'FEB', COUNT (ro_no))
) "FEB",
MAX (DECODE (TO_CHAR (ro_dtime, 'MON'), 'MAR', COUNT (ro_no))
) "MAR",
MAX (DECODE (TO_CHAR (ro_dtime, 'MON'), 'APR', COUNT (ro_no))
) "APR",
MAX (DECODE (TO_CHAR (ro_dtime, 'MON'), 'MAY', COUNT (ro_no))
) "MAY",
MAX (DECODE (TO_CHAR (ro_dtime, 'MON'), 'JUN', COUNT (ro_no))
) "JUN"
FROM mrh
WHERE sts != 'X'
AND ro_date BETWEEN TO_DATE ('01-JAN-08 00:00:00',
'dd-mon-yy hh24:mi:ss'
)
AND TO_DATE ('30-JUN-08 23:59:59',
'dd-mon-yy hh24:mi:ss'
)
AND svmd_code IN
('HEAVY DUTY', 'BS', 'COM',
'MEDIUM DUTY', 'LIGHT DUTY', 'TF',
'VIV')
GROUP BY SVMD_CODE;

I wanted the result like this
SVMD_CODE JAN FEB MAR APR MAY JUN
HEAVY DUTY 10 20 5 9 10 15
BS 15 12 4 3
COM 10 13 5
MEDIUM DUTY 12 12 6
LIGHT DUTY 5 15 7
TF 3 4 10
VIV 1 3 12

Anyway to solve my above query?
Re: Help in Pivot Query (Error ORA-00937: not a single-group group function) [message #331021 is a reply to message #331014] Wed, 02 July 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Help in Pivot Query (Error ORA-00937: not a single-group group function) [message #331024 is a reply to message #331021] Wed, 02 July 2008 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that your problem is the MAX(...COUNT(*)) construct.

Try replacing those lines with:
 SUM(DECODE (TO_CHAR (ro_dtime, 'MON'), 'JAN', 1,0)) JAN,
Re: Help in Pivot Query (Error ORA-00937: not a single-group group function) [message #331025 is a reply to message #331024] Wed, 02 July 2008 03:11 Go to previous message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi

Thank you very much it works now.
Previous Topic: circular sql ?
Next Topic: BETWEEN
Goto Forum:
  


Current Time: Thu Dec 08 18:03:57 CST 2016

Total time taken to generate the page: 0.10966 seconds