VBA ADODB: ORA00979: not a group by expression

Question

I don't know why I have the error on sum function :"ORA-00979: not a GROUP BY expression":

My code is :

 RECSET2.Open " select dossier.NO_POLICE, ev1.D_EFFET, ev1.ID_FAMILLE_PORTEF, ev1.ID_PORTEFEUILLE, gr.LB_COURT_GR_EVT, pers1.S_PRENOM||' '||pers1.S_NOM as Collaborateur, proto.CD_PROTOCOLE, ev1.ID_FAMILLE_PORTEF,comm.L_COMMENT_DOSSIER," & _
" dossier.UI_CREATION, ev1.LP_STATUT_EVT, sum(ev3.MT_BRUT) as Ecart, ev1.MT_BRUT, tiers2.CD_TIERS as Tmandataire, pers3.S_RAISONSOC as Mandataire,tiers1.CD_TIERS as Tdepositaire, pers2.S_RAISONSOC as Depositaire, ev1.IS_EVENEMENT from DB_DOSSIER dossier left join DB_EVENEMENT ev1 " & _
" on dossier.IS_DOSSIER = ev1.IS_DOSSIER  left join DB_EVENEMENT ev2 on ev1.IS_EVENEMENT=ev2.IS_EVENEMENT_PERE  left join DR_LIEN_EVT drevl on ev2.IS_EVENEMENT=drevl.IS_EVENEMENT left join DB_EVENEMENT ev3 on drevl.IS_EVT_LIE=ev3.IS_EVENEMENT  left join DP_CLASSE_EVT cl on ev1.IS_CLASSE_EVT=cl.IS_CLASSE_EVT " & _
" left join DP_GROUPE_EVT  gr on cl.IS_GR_EVT=gr.IS_GR_EVT left join DB_COMMENT_DOSSIER comm on dossier.IS_DOSSIER=comm.IS_DOSSIER left join DR_COLLABORATEUR_PROTOCOLE collabproto on dossier.IS_PROTOCOLE=collabproto.IS_PROTOCOLE left join DB_COLLABORATEUR collab on collabproto.IS_COLLABORATEUR=collab.IS_COLLABORATEUR " & _
" left join DB_PERSONNE pers1 on collab.IS_PERSONNE=pers1.IS_PERSONNE left join DB_PROTOCOLE  proto on dossier.IS_PROTOCOLE=proto.IS_PROTOCOLE left join DB_PORTEFEUILLE  portef1 on ev1.ID_FAMILLE_PORTEF=portef1.ID_FAMILLE_PORTEF and  ev1.ID_PORTEFEUILLE=portef1.ID_PORTEFEUILLE left join DB_TIERS  tiers1 on tiers1.IS_TIERS=portef1.IS_TIERS_DEPOSITAIRE  " & _
" left join DB_PERSONNE  pers2 on tiers1.IS_PERSONNE=pers2.IS_PERSONNE left join DB_TIERS tiers2 on tiers2.IS_TIERS=portef1.IS_TIERS_GESTIONNAIRE left join DB_PERSONNE  pers3 on tiers2.IS_PERSONNE=pers3.IS_PERSONNE " & _
" where dossier.CD_DOSSIER in ('COROP','COROC') and dossier.LP_ETAT_DOSS not in ('CLOSE','ANNUL','A30') and ev1.D_EFFET>='" & Ma_date & "' and ev1.IS_EVENEMENT_PERE is null and drevl.LP_LIEN_EVT in ('COROP','COROC') group by ev1.NO_POLICE, ev1.IS_EVENEMENT, ev1.D_EFFET, dossier.UI_CREATION ", cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

Thank you for help !

@Rory thank you, do I need to put in GROUP BY statement all the variables from select statement ? I would like to calculate the sum by ev1.NO_POLICE, ev1.IS_EVENEMENT, ev1.D_EFFET, dossier.UI_CREATION — VBA_Anne_Marie, Apr 07 '21 at 11:33
This is not a VBA or Excel-question but pure SQL. You need to understand the usage and logic of aggregate functions (eg SUM) and the group-by clause. Obviously you are using Oracle: Use the SQL Developer or a similar tool and try to figure out the correct statement before putting it into VBA. — FunThomas, Apr 07 '21 at 11:35
@FunThomas thank you for the advise. I tried this code on SAS (proc sql with group by ev1.NO_POLICE, ev1.IS_EVENEMENT, ev1.D_EFFET, dossier.UI_CREATION). The code worked. I know the correct statement for SQL. I'm newer in VBA, I just don't know how to translate my code on VBA. — VBA_Anne_Marie, Apr 07 '21 at 11:39
@Rory When I put a group by statement I have another error :not a group by expressin. I don't know why beacause I already selected all columns of group by in SELECT statement... — VBA_Anne_Marie, Apr 07 '21 at 11:42
When you have a SQL statement that works, it will work identically when issued in VBA. — FunThomas, Apr 07 '21 at 11:43
@FunThomas so i didn't use the GROP BY statement beacuse I had an error : NOT A GROUP BY EXPRESSION. I used select distinct, but, yes, it doesn't work for sum statement. I don't know how to use GROUP BY an SUM together. :( — VBA_Anne_Marie, Apr 07 '21 at 11:45
@FunThomas yes, I copied the code identically but group by doesn't work. I don't know why. The error was NOT A GROUP BY EXPRESSION. Bu I selected all columns in group by in SELECT statement. — VBA_Anne_Marie, Apr 07 '21 at 11:47
As you see, this is an error issued by Oracle, not VBA. Something is wrong with your SQL statement. Write the statement into a variable before issuing it, put a Debug.Print statement before you issue the Open-statement, Cut&Paste the statement from the immediate window and issue it directly against the database - you will get the same error. Check and correct the statement. As I said, this is *not* a VBA problem. — FunThomas, Apr 07 '21 at 11:51
@FunThomas I update my main message, I put my previous code that works on SAS. — VBA_Anne_Marie, Apr 07 '21 at 11:53
I don't know SAS, maybe it does some magic, but you have a dozen or so fields in the Select-Clause, only one of it uses an aggregate function (ev3.MT_BRUT) but you have only 4 fields in the group by clause. This will not work (per definition). See https://stackoverflow.com/a/1520641/7599798 — FunThomas, Apr 07 '21 at 12:03
@FunThomas I mus put all columns from select to GROUP BY, except ev3.MT_BRUT ? — VBA_Anne_Marie, Apr 07 '21 at 13:11
This is how Group By works (and it can't work any other way) — FunThomas, Apr 07 '21 at 13:14
@FunThomas thank you, I didn't know. In SAS (proc sql) I can use only some columns. — VBA_Anne_Marie, Apr 07 '21 at 13:17
And what does SAS return when a Select finds different values for a field that is not part of the group by-clause? — FunThomas, Apr 07 '21 at 13:19
@FunThomas I put in group by only the columns with different values. — VBA_Anne_Marie, Apr 07 '21 at 13:46
@FunThomas when I apply the SUM(ev3.MT_BRUT) I lost the line with ev3.MT_BRUT=. Is it normal ? — VBA_Anne_Marie, Apr 07 '21 at 13:47
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230855/discussion-between-marie-and-funthomas). — VBA_Anne_Marie, Apr 07 '21 at 15:52

score 0 · Answer 1 · answered Apr 07 '21 at 15:50

Group by all the columns except the aggregate one

GROUP BY dossier.NO_POLICE, ev1.D_EFFET, ev1.ID_FAMILLE_PORTEF, 
 ev1.ID_PORTEFEUILLE, gr.LB_COURT_GR_EVT,
 pers1.S_PRENOM||' '||pers1.S_NOM, proto.CD_PROTOCOLE, ev1.ID_FAMILLE_PORTEF,
 comm.L_COMMENT_DOSSIER, dossier.UI_CREATION, ev1.LP_STATUT_EVT,
 ev1.MT_BRUT, tiers2.CD_TIERS, pers3.S_RAISONSOC,
 tiers1.CD_TIERS, pers2.S_RAISONSOC, ev1.IS_EVENEMENT

VBA ADODB: ORA00979: not a group by expression

1 Answers1