Not A Group By Expression

My query is below and I had it working but then modified it and now I am getting not a group by expression.  I have googled and know what it means but I cannot see what I have done wrong below.  Someone please help. Either point out the error or maybe tell me how to rewrite?  Thanks.

select cor.error_desc, sum(cor.error_count), trunc(sum(cor.error_count)/(d.errorcount) * 100, 2) MYTABLE cor, (select sum(c.error_count) as errorcount from MYTABLE c where c.las='ANAME' and trunc(c.date_stamp) between to_date('3/1/2012', 'MM/DD/YYYY') and to_date('3/31/2012', 'MM/DD/YYYY') and c.major is not null) d where cor.las='ANAME' and trunc(cor.date_stamp) between to_date('3/1/2012', 'MM/DD/YYYY') and to_date('3/31/2012', 'MM/DD/YYYY')  and cor.major is not null group by cor.error_desc
LVL 1
thamilto0410Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

micropc1Commented:
.
thamilto0410Author Commented:
micropc1: Comment?
slightwv (䄆 Netminder) Commented:
>>My query is below and I had it working but then modified it

What was the before?

I agree with micropc1's first comment.  Not sure why they deleted it.  I believe you have a problem with:
trunc(sum(cor.error_count)/(d.errorcount) * 100, 2) MYTABLE cor,



I don't see a FROM in the outer query.

There is no from here:
...
      ) d
where cor.las='ANAME' and
...

If you indent it so everything lines up you will see it.

select cor.error_desc,
	sum(cor.error_count),
	trunc(sum(cor.error_count)/(d.errorcount) * 100, 2) MYTABLE cor,
	(
		select sum(c.error_count) as errorcount
		from MYTABLE c
		where c.las='ANAME' and
			trunc(c.date_stamp) between to_date('3/1/2012', 'MM/DD/YYYY') and
				to_date('3/31/2012', 'MM/DD/YYYY') and c.major is not null
	) d
where cor.las='ANAME' and
	trunc(cor.date_stamp) between to_date('3/1/2012', 'MM/DD/YYYY') and
		to_date('3/31/2012', 'MM/DD/YYYY')  and
	cor.major is not null
group by cor.error_desc

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

thamilto0410Author Commented:
slightwv : I checked the original query there is a from in the outer query I just must have missed putting it in when I wrote the question.  As for the before I AM AN IDIOT because I did not save the working version as something else before I modified what I had already working and I cannot remember the exact syntax now.  If I need
Desc                   Total Major Count    TotalMajorCount/TotalForAllColumnsByDateRange
SOMTHING             10                               10/48      (the 48 is the d.errorcount)

How do I rewrite then?
slightwv (䄆 Netminder) Commented:
>>there is a from in the outer query I just must have missed putting it in when I wrote the question

And what was it?  Without a from, I cannot even guess at a sample table...


Give us table definitions, sample data and expected results and I'm sure one of us will provide working code.
micropc1Commented:
Sorry, I felt like I might not have spent enough time examining the query before I originally posted, so I wanted to take another look first.

I think the issue is actually with the subquery - it needs to be defined in the group by. Also, you should reference it as 'd' - not d.errorcount - since d references a single value not an entire table. Also the FROM clause is missing as slightvm mentioned. So I'm thinking something like this might work...

select cor.error_desc
	, sum(cor.error_count)
	, trunc(sum(cor.error_count)/(d) * 100, 2) 
	, (select sum(c.error_count) as errorcount from MYTABLE c 
		where c.las='ANAME' 
		and trunc(c.date_stamp) between to_date('3/1/2012', 'MM/DD/YYYY') 
		and to_date('3/31/2012', 'MM/DD/YYYY') 
		and c.major is not null) d 
from MYTABLE cor
where cor.las='ANAME' 
and trunc(cor.date_stamp) between to_date('3/1/2012', 'MM/DD/YYYY') 
and to_date('3/31/2012', 'MM/DD/YYYY') 
and cor.major is not null 
group by cor.error_desc, d

Open in new window



I can't remember offhand if the alias will work through - you might have to use the whole subquery.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thamilto0410Author Commented:
micropc1:  Thank you I will give this an attempt in the morning the oracle  DB that supports this app is at work.  Also my SQL skills are weak what does this "I can't remember offhand if the alias will work through - you might have to use the whole subquery" mean?
micropc1Commented:
i can't remember if you can just do group by d or if you need to do group by (select sum(c.error_count) from...
thamilto0410Author Commented:
micropc1: Thanks.  I'll let you know tomorrow.
slightwv (䄆 Netminder) Commented:
I would still like to see some sample data and expected results.

There might be easier and more efficient ways.

Also, what is your Oracle version?
micropc1Commented:
Right - I can't guarantee that's the problem - I haven't tested it or anything. It just makes since to me since d is the only non-aggregate that isn't declared in the group by clause. Definitely curious to see the results myself..
thamilto0410Author Commented:
slightwv:  Thank you for attempting to help me.

micropc1:  DEAD ON.  I had to tweak what you have above just a little and it is working perfectly and runs within a couple of seconds.  You both have a great day.
thamilto0410Author Commented:
slightwv:  Thank you for your help.  This site and the assistance from experts is bettering the skills of many.
micropc1:  DEAD ON.  My query skills are still a little weak so I had it wrong.  The query you provided only had to be slightly tweaked and it is working perfectly and runs in about 2 seconds and I have already manually validated that what is being pulled is what has been requested.  MY SINCERE THANKS TO YOU BOTH.
micropc1Commented:
Glad to hear its working. Have a good day.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.