Solved

Not A Group By Expression

Posted on 2012-03-20
15
470 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
  • 6
  • 5
  • 3
15 Comments
 
LVL 7

Expert Comment

by:micropc1
Comment Utility
.
0
 
LVL 1

Author Comment

by:thamilto0410
Comment Utility
micropc1: Comment?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 1

Author Comment

by:thamilto0410
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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 500 total points
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 7

Expert Comment

by:micropc1
Comment Utility
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
Comment Utility
micropc1: Thanks.  I'll let you know tomorrow.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Glad to hear its working. Have a good day.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article offers some helpful and general tips for safe browsing and online shopping. It offers simple and manageable procedures that help to ensure the safety of one's personal information and the security of any devices.
Google currently has a new report that is in beta and coming soon to Webmaster Tool accounts. This Micro Tutorial will highlight new features for Google Webmaster Tools.
Shows how to create a shortcut to site-search Experts Exchange using Google in the Chrome browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch the Search Engine Menu: In chrome, via you…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now