Home » SQL & PL/SQL » SQL & PL/SQL » Help with writing a query to multiply columns (Oracle 11g)
icon5.gif  Help with writing a query to multiply columns [message #571509] Mon, 26 November 2012 12:37 Go to next message
raymak
Messages: 15
Registered: November 2012
Junior Member
Hi, I am writing a query and I am trying to multiply some of the columns with *1.50 and 0.75.

The columns I am trying to multiply are coke_rebate.volumecsd and coke_rebate.volumencb. I am getting an error in oracle sql command from web " ORA-00937: not a single-group group function"

Here is the code I am writing:

SELECT CUSTOMERS.CUSTID,
       CUSTOMERS.MEMBERID,
       CUSTOMERS.BNAME,
       COKE_REBATE.COKEID,
       SUM(COKE_REBATE.VOLUMECSD*1.50) "TOTALCSD",
       SUM(COKE_REBATE.VOLUMENCB*0.75) "TOTALNCB",
       DRPEPPER_REBATE.DRPEPID,
       DRPEPPER_REBATE.TOTALCARB,
       DRPEPPER_REBATE.TOTALNCB
FROM CUSTOMERS
    FULL JOIN CUST_COKEID
ON CUSTOMERS.CUSTID = CUST_COKEID.CUSTID
    FULL JOIN COKEID
ON CUST_COKEID.COKEID = COKEID.COKEID
    FULL JOIN COKE_REBATE
ON COKEID.COKEID = COKE_REBATE.COKEID 
    FULL JOIN CUST_DRPEPID
ON CUSTOMERS.CUSTID = CUST_DRPEPID.CUSTID
    FULL JOIN DRPEPID
ON CUST_DRPEPID.DRPEPID = DRPEPID.DRPEPID
    FULL JOIN DRPEPPER_REBATE
ON DRPEPID.DRPEPID = DRPEPPER_REBATE.DRPEPID 
 


I dont know what I am doing wrong, but any help I will appreciate it.

Thanks,

Ray Mak
Re: Help with writing a query to multiply columns [message #571515 is a reply to message #571509] Mon, 26 November 2012 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have no GROUP BY clause.
It is mandatory in Oracle.

Regards
Michel
Re: Help with writing a query to multiply columns [message #571520 is a reply to message #571515] Mon, 26 November 2012 18:18 Go to previous messageGo to next message
raymak
Messages: 15
Registered: November 2012
Junior Member
SELECT CUSTOMERS.CUSTID,
       CUSTOMERS.MEMBERID,
       CUSTOMERS.BNAME,
       COKE_REBATE.COKEID,
       SUM(COKE_REBATE.VOLUMECSD*1.50) as TOTALCSD,
       SUM(COKE_REBATE.VOLUMENCB*0.75) as TOTALNCB,
       DRPEPPER_REBATE.DRPEPID,
       DRPEPPER_REBATE.TOTALCARB,
       DRPEPPER_REBATE.TOTALNCB
FROM CUSTOMERS
    FULL JOIN CUST_COKEID
ON CUSTOMERS.CUSTID = CUST_COKEID.CUSTID
    FULL JOIN COKEID
ON CUST_COKEID.COKEID = COKEID.COKEID
    FULL JOIN COKE_REBATE
ON COKEID.COKEID = COKE_REBATE.COKEID 
    FULL JOIN CUST_DRPEPID
ON CUSTOMERS.CUSTID = CUST_DRPEPID.CUSTID
    FULL JOIN DRPEPID
ON CUST_DRPEPID.DRPEPID = DRPEPID.DRPEPID
    FULL JOIN DRPEPPER_REBATE
ON DRPEPID.DRPEPID = DRPEPPER_REBATE.DRPEPID
GROUP BY COKE_REBATE.VOLUMENCB, COKE_REBATE.VOLUMECSD


It says not a group by expression?
Re: Help with writing a query to multiply columns [message #571521 is a reply to message #571520] Mon, 26 November 2012 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>It says not a group by expression?
Believe it because it is true.
Which columns should be included in GROUP BY & which columns should be excluded?

Re: Help with writing a query to multiply columns [message #571532 is a reply to message #571521] Mon, 26 November 2012 21:23 Go to previous messageGo to next message
raymak
Messages: 15
Registered: November 2012
Junior Member
Thats a good question BlackSwan. I tried coke_rebate.cokeid and it gave me the same error.
Re: Help with writing a query to multiply columns [message #571536 is a reply to message #571532] Mon, 26 November 2012 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
a wise man once told me, first make it work; then make it fancy.

 1* select department_id, sum(salary) from employees group by department_id order by 1
SQL> /

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        4400
           20       19000
           30       24900
           40        6500
           50      156400
           60       28800
           70       10000
           80      304500
           90       58000
          100       51600
          110       20300

DEPARTMENT_ID SUM(SALARY)
------------- -----------
                     7000

12 rows selected.

Re: Help with writing a query to multiply columns [message #571550 is a reply to message #571532] Tue, 27 November 2012 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
raymak wrote on Tue, 27 November 2012 04:23
Thats a good question BlackSwan. I tried coke_rebate.cokeid and it gave me the same error.


All columns that are not in an aggregate function must be in the GROUP BY clause.

Regards
Michel
Re: Help with writing a query to multiply columns [message #571912 is a reply to message #571520] Mon, 03 December 2012 22:08 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

there are 7 columns that needs to be grouped.


    GROUP BY CUSTOMERS.CUSTID,
       CUSTOMERS.MEMBERID,
       CUSTOMERS.BNAME,
       COKE_REBATE.COKEID,
       DRPEPPER_REBATE.DRPEPID,
       DRPEPPER_REBATE.TOTALCARB,
       DRPEPPER_REBATE.TOTALNCB





[Updated on: Mon, 03 December 2012 22:09]

Report message to a moderator

Previous Topic: Date Functions
Next Topic: Sequence max value vs database performance
Goto Forum:
  


Current Time: Sat Oct 25 10:06:08 CDT 2014

Total time taken to generate the page: 0.17241 seconds