Home » SQL & PL/SQL » SQL & PL/SQL » What am I doing wrong? Alias ...
What am I doing wrong? Alias ... [message #356420] Thu, 30 October 2008 12:33 Go to next message
imdreaming
Messages: 11
Registered: April 2008
Junior Member
This is not my day. I got the inline SQL select working, thanks to help from here.

Now, I am folding that back into my overall query and having trouble:

SELECT count(qt.body_id) field1,
(SELECT SUM (adjustment)
FROM end e
WHERE e.body_id = qt.body_id
AND e.status_id(+)=1302) as nfield
FROM body rb, quote qt
WHERE rb.body_id = qt.body_id
group by nfield;


The group by is not recognizing the alias. I've also tried it with just (subquery text) nfield

Thanks in advance.
Re: What am I doing wrong? Alias ... [message #356423 is a reply to message #356420] Thu, 30 October 2008 12:52 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
you sub query returns only single value i.e., SUM (adjustment).

How Group by will work on that??

What is your requirement?
Re: What am I doing wrong? Alias ... [message #356425 is a reply to message #356423] Thu, 30 October 2008 12:58 Go to previous messageGo to next message
imdreaming
Messages: 11
Registered: April 2008
Junior Member
When I change the query to this (i.e., remove the count(...) from qt.body_id)

SELECT qt.body_id field1,
(SELECT SUM (adjustment)
FROM end e
WHERE e.body_id = qt.body_id
AND e.status_id(+)=1302) as nfield
FROM body rb, quote qt
WHERE rb.body_id = qt.body_id;


It works.

But my requirement is that I get a count of qt.body_id. The way my data is structured, this subquery the only way I am able to get valid sum from table end.

ETA: I have another value I can add in to group on. I have posted a very simplified version of what I'm doing.

Thanks in advance.

[Updated on: Thu, 30 October 2008 13:02]

Report message to a moderator

Re: What am I doing wrong? Alias ... [message #356429 is a reply to message #356425] Thu, 30 October 2008 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can count without using "group by" if you count all occurrences without grouping.

Regards
Michel
Re: What am I doing wrong? Alias ... [message #356435 is a reply to message #356425] Thu, 30 October 2008 13:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about you posting some test data to play with and ofcourse with the expected output. It will be lot more easier for someone to help you with other alternative if feasible.

Regards

Raj
Re: What am I doing wrong? Alias ... [message #356438 is a reply to message #356435] Thu, 30 October 2008 13:20 Go to previous message
imdreaming
Messages: 11
Registered: April 2008
Junior Member
Will post sample data shortly.
Previous Topic: SQL Command to list tables
Next Topic: fuction in my where condition
Goto Forum:
  


Current Time: Fri Dec 02 18:42:14 CST 2016

Total time taken to generate the page: 0.09280 seconds