Select A, SUM(A) 1

[boggg1]

How do I do the following...

SELECT A, B, C, SUM(A)
FROM X

I found the following on an Oracle error site...

ORA-00937: not a single-group group function

Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.

...which presumably means I need a GROUP BY but I cannot seem to get it right.

I could run this as two queries (SELECT A, B, C ...and... SELECT SUM(A) but it seems a waste.

 

[JarlH]

Perhaps:

SELECT A, B, C, (SELECT SUM(A) FROM X)
FROM X

or

SELECT A, B, C, SUM(A)
FROM X
GROUP BY A, B, C


HTH,
Jarl

 

[boggg1]

When I try the second method I get data such as...

25 B1 C1 24
25 B2 C2 24
2 B3 C3 1
3 B4 C4 2

SUM(C) is always 1 less than C !

Where is it getting this last value (the SUM) from ? There are no negative values in the entire column.
I should say that the real query is considerably more complex, using multiple joined tables and where clauses. But nevertheless the Sum is not being performed as wanted.

_______________________

I have avoided the first method because the real query is 30 lines long and to put a SELECT inside a SELECT would make it 60 lines or so. I think.

 

[r937]

JarlH's queries are correct

the problem lies in your scenario, which you have regrettably oversimplified



r937.com | rudy.ca

 

[boggg1]

OK, I'll do it as two queries.

Thanks