Put a sum in the filters of a SQL query?

Question

Put a sum in the filters of a SQL query?

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

Good morning, I'm scheduling this query:

SELECT t1.percod permisor, t1.pernom ciudad,
       t2.abocod cod_abonado, t2.abonom1 nombre, t2.aboape1 apellido,
       t2.abosts, t2.abocmsts, t2.abotlfsts, t2.aboipsts,
       t3.recicod, t3.recifch, t3.reciimp monto_recibo, 
       (t3.reciimp - sum(t4.recifacimp)) monto_sin_aplicar
FROM gx.permis t1,
     gx.abonad t2,
     gx.recibo t3,
     gx.recide t4
WHERE t1.percod = t2.percod
  AND t2.percod = t3.percod
  AND t2.abocod = t3.abocod
  AND t3.percod = t4.percod
  AND t3.recicod = t4.recicod
GROUP BY t1.percod, t1.pernom, t2.abocod, t2.abonom1, t2.aboape1, t2.abosts, t2.abocmsts,t2.abotlfsts, t2.aboipsts, t2.abodthsts, t3.recicod, t3.recifch, t3.reciimp;

What I need is that the query does not show those data where (t3.reciimp - sum(t4.recifacimp)) equals zero (0) ... I tried adding AND (t3.reciimp - sum(t4.recifacimp)) <> 0 but gives this error:

ORA-00934: group function not allowed here   00934. 00000 - "group function is not allowed here"   * Cause:
  * Action:   Error in the line: 19, column: 23

How do I resolve that error?

sql oracle-11g

asked by GioV 02.02.2017 в 14:13

source

2 answers

1

The SQL standard includes the clause having , which serves to filter the result of a query after having grouped.

Although the Pablo response gives you a solution, in my humble opinion, it is more difficult to interpret and to maintain it.

Try this:

select   t1.percod permisor
       , t1.pernom ciudad
       , t2.abocod cod_abonado
       , t2.abonom1 nombre
       , t2.aboape1 apellido
       , t2.abosts
       , t2.abocmsts
       , t2.abotlfsts
       , t2.aboipsts
       , t3.recicod
       , t3.recifch
       , t3.reciimp monto_recibo
       , (t3.reciimp - sum(t4.recifacimp)) monto_sin_aplicar
  from gx.permis t1
       inner join gx.abonad t2 on t1.percod = t2.percod
       inner join gx.recibo t3 on t2.percod = t3.percod and t2.abocod = t3.abocod
       inner join gx.recide t4 on t3.percod = t4.percod and t3.recicod = t4.recicod
 group by  t1.percod
       , t1.pernom
       , t2.abocod
       , t2.abonom1
       , t2.aboape1
       , t2.abosts
       , t2.abocmsts
       , t2.abotlfsts
       , t2.aboipsts
       , t2.abodthsts
       , t3.recicod
       , t3.recifch
       , t3.reciimp
having t3.reciimp - sum(t4.recifacimp) != 0;

I have taken the liberty to re-write your query using the join syntax of SQL 92, since I think it also helps to make clear what conditions are to relate tables and what would be for filter records.

Unless you're using a very old version of Oracle, you're not going to have problems with it.

answered by 02.02.2017 в 16:13

Spam filter, operation of MultinomialNB (sklearn.naive_bayes) PHP problem with abstract classes and protected functions

score 1 · Accepted Answer

Make a query that returns the difference between those two fields and filters through it. Something like this:

select * from (SELECT t1.percod permisor, t1.pernom ciudad,
       t2.abocod cod_abonado, t2.abonom1 nombre, t2.aboape1 apellido,
       t2.abosts, t2.abocmsts, t2.abotlfsts, t2.aboipsts,
       t3.recicod, t3.recifch, t3.reciimp monto_recibo, 
       (t3.reciimp - sum(t4.recifacimp)) monto_sin_aplicar,
       (t3.reciimp - sum(t4.recifacimp)) diferencia
FROM gx.permis t1,
     gx.abonad t2,
     gx.recibo t3,
     gx.recide t4
WHERE t1.percod = t2.percod
  AND t2.percod = t3.percod
  AND t2.abocod = t3.abocod
  AND t3.percod = t4.percod
      AND t3.recicod = t4.recicod
    GROUP BY t1.percod, t1.pernom, t2.abocod, t2.abonom1, t2.aboape1, t2.abosts, t2.abocmsts,
t2.abotlfsts, t2.aboipsts, t2.abodthsts, t3.recicod, t3.recifch, t3.reciimp)
    where diferencia=0;