Solved

How can I group and sumarize dollar amounts by sales office, sales rep, and client

Posted on 2013-01-22
3
613 Views
Last Modified: 2013-01-22
How can I group and sumarize dollar amounts by sales office, sales rep, and client  

I keep getting error with code below:

[1]: ORA-00937: not a single-group group function

Select extract(month from rslcd.rfp_requested_beg_dt) as rmonth, sum(rslf.rfp_est_annual_premium_amt)
from adm.rfp_sl_fact rslf
join adm.b_rfp_sl_coverage_dim rslcd on rslf.rfp_sl_coverage_key = rslcd.rfp_sl_coverage_key
where  extract(month from rslcd.rfp_requested_beg_dt) = 10
and extract(year from rslcd.rfp_requested_beg_dt) = 2011
0
Comment
Question by:conardb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38807235
You group by all non-aggregate columns.

From the SQL you posted, you SUM on rslf.rfp_est_annual_premium_amt, so you need to group by extract(month from rslcd.rfp_requested_beg_dt).

If you add additional columns for sales office, sales rep, and client , you will need to group by those as well.

If you can provide some sample data and expected results, and what reporting tool you are using we can probably provide a working model.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 250 total points
ID: 38807237
Syntax:

select col1, col2, sum(col3) from table
Where Col3 <> condition_1
group by Col1, col2
having count(*) > condition_2

In your statement, group by statement is missing.
0
 

Author Closing Comment

by:conardb
ID: 38807364
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL Query - Group By Year 3 55
SQL Syntax Question 9 56
Oracle Join issue. 3 47
Oracle SQL Developer equivalent MS SQL 6 34
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question