[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Not A Group By Expression

Posted on 2012-03-20
15
Medium Priority
?
482 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:thamilto0410
[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
  • 6
  • 5
  • 3
15 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37745230
.
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 37745276
micropc1: Comment?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37745314
>>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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:thamilto0410
ID: 37745343
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?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37745357
>>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.
0
 
LVL 7

Accepted Solution

by:
micropc1 earned 2000 total points
ID: 37745359
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.
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 37745392
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?
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37745406
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...
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 37745422
micropc1: Thanks.  I'll let you know tomorrow.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37745429
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?
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37745437
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..
0
 
LVL 1

Author Comment

by:thamilto0410
ID: 37746913
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.
0
 
LVL 1

Author Closing Comment

by:thamilto0410
ID: 37748840
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.
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37748877
Glad to hear its working. Have a good day.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to take different types of Oracle backups using RMAN.
This Micro Tutorial will demonstrate how to add subdomains to your content reports. This can be very importing in having a site with multiple subdomains.

656 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