Solved

Not A Group By Expression

Posted on 2012-03-20
15
477 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 500 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

#Citrix #Internet Explorer #Enterprise Mode #IE 11 #IE 8
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

691 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