Solved

Not A Group By Expression

Posted on 2012-03-20
15
476 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
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
Technology Partners: 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 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

696 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