How can i use two fields in a SELECT clause that has a "single-group group function"

Question

i need to write a query that joins a lot of queries. They are connected by an ID. The problem is that in one of the queries, the value I have to select is a "single-group group function" and i must evaluate a condition on the ON clause that must use a second selected column, that i'm unable to add. I'll write a lot easier example than my code:

Select frst.FirstResult, scnd.FirstResult 
from(
    Select something AS FirstResult, ID_to_compare as SecondResult from table
    Where -- [...]
) frst

join(
    Select something AS FirstResult, ID_to_compare as SecondResult from table2
    Where -- [...] 
) scnd 
ON frst.SecondResult=scnd.SecondResult

join(
    Select something AS FirstResult, ID_to_compare as SecondResult from table3
    Where -- [...]
) trd 
ON scnd.SecondResult=trd.SecondResult

-- [...]

join(
    Select single_group_function(params) AS FirstResult, ID_to_compare as SecondResult from table3
--This select cannot be done because of the group function cannot be executed
    Where -- [...]
) trd 
ON svn.SecondResult=eit.SecondResult

My problem is that i need to compare the "SecondResult" from every select, but the member group is not allowed to make a query like this. I've tried with the "dual" table but it's really messy for me and i don't understand exactly how to use it. I've also tried by joining them in a macro query, but every single select is big enough to be really confusing to write. I've taken the ideas from this post and a lot more. Have you any hint to acomplish my task?

For petition of MT0, a Minimal Complete and verificable problem is this:

Select frst.FirstField, scnd.FirstField, frst.SecondField
from
(Select a1.DUMMY as FirstField, a2.DUMMY as SecondField
    from dual a1 join dual a2 on 1=1) frst
  join
(Select a1.DUMMY as FirstField, a2.DUMMY as SecondField
    from dual a1 join dual a2 on 1=1) scnd
  ON frst.SecondField = scnd.SecondField

  join
  (Select sum(a1.DUMMY) as FirstField, a2.DUMMY as SecondField
    from dual a1 join dual a2 on 1=1) trd
  ON trd.SecondField = scnd.SecondField
  ;

I would expect in this case

frst.FirstField  scnd.FirstField  frst.SecondField
---------------- ---------------- --------------------
X                X                1

But i get instead

Error en la línea de comandos : 11 Columna : 40
Informe de error -
Error SQL: ORA-00937: la función de grupo no es de grupo único
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:

Can you give a [MCVE] with DDL/DML statements for some sample data and your expected output to give an illustration of a minimal version of the problem. — MT0, Apr 06 '17 at 10:39

score 2 · Accepted Answer · answered Apr 06 '17 at 11:13

This is your sample subquery:

Select sum(a1.DUMMY) as FirstField, a2.DUMMY as SecondField
from dual a1 join dual a2 on 1=1

So do you expect one result row? This is what an aggregation with no GROUP BY does. You'd get the sum over all records, but which a2.DUMMY to show in this result record? This is what the DBMS is complaining about.

What you probably want is a sum per a2.DUMMY instead. So group by this column:

Select sum(a1.DUMMY) as FirstField, a2.DUMMY as SecondField
from dual a1 join dual a2 on 1=1
GROUP BY a2.DUMMY

score 2 · Answer 2 · answered Apr 06 '17 at 11:15

In your simplified example you're doing:

  join
  (Select sum(a1.DUMMY) as FirstField, a2.DUMMY as SecondField
    from dual a1 join dual a2 on 1=1) trd
  ON trd.SecondField = scnd.SecondField

You can simplify further to see the problem:

Select sum(a1.DUMMY) as FirstField, a2.DUMMY as SecondField
from dual a1 join dual a2 on 1=1;

ORA-00937: not a single-group group function

You have to include any columns that you are not aggregating in the group-by clause:

Select sum(a1.DUMMY) as FirstField, a2.DUMMY as SecondField
from dual a1 join dual a2 on 1=1
group by a2.DUMMY;

which will now get ORA-01722, since dummy is a string; using max instead:

Select frst.FirstField, scnd.FirstField, frst.SecondField
from
(Select a1.DUMMY as FirstField, a2.DUMMY as SecondField
    from dual a1 join dual a2 on 1=1) frst
  join
(Select a1.DUMMY as FirstField, a2.DUMMY as SecondField
    from dual a1 join dual a2 on 1=1) scnd
  ON frst.SecondField = scnd.SecondField

  join
  (Select max(a1.DUMMY) as FirstField, a2.DUMMY as SecondField
    from dual a1 join dual a2 on 1=1 group by a2.DUMMY) trd
  ON trd.SecondField = scnd.SecondField
  ;

F F S
- - -
X X X

So you real query needs to do something like:

    Select single_group_function(params) AS FirstResult, ID_to_compare as SecondResult from table3
    Where -- [...]
    group by ID_to_compare

in its subquery

How can i use two fields in a SELECT clause that has a "single-group group function"

2 Answers2