Not a single-group group function when I execute my SQL

Question

I get this error when I execute my SQL, state: Not a single-group group function :

[SQL]select distinct bd.name aaa,
       substr(m.code,0,4) as mcode,
       sum(p.nassistnum) BB,
       sum(case when p.vbdef4='~' then '0' else p.vbdef4 end) CC
        from bd_material_v m 
        inner join ic_purchasein_b p
           on m.pk_source=p.cmaterialvid
        inner join ic_purchasein_h h 
           on  h.cgeneralhid = p.cgeneralhid and  h.ctrantypeid='1001A210000000002UW7' and h.vdef8='~'
        inner join bd_marbasclass bd
           on bd.code=substr(m.code,0,4)
        where m.creator='1001A210000000000IIQ' 
      order by substr(m.code,0,4)        
    --  and h.pk_org in (parameter('param3'))
                    --    and substr(h.dbilldate,1,10) >= parameter('param1')
                     --   and substr(h.dbilldate,1,10) <= parameter('param2')
[Err] ORA-00937: not a single-group group function

The SQL is below:

select distinct bd.name aaa,
       substr(m.code,0,4) as mcode,
       sum(p.nassistnum) BB,
       sum(case when p.vbdef4='~' then '0' else p.vbdef4 end) CC
        from bd_material_v m 
        inner join ic_purchasein_b p
           on m.pk_source=p.cmaterialvid
        inner join ic_purchasein_h h 
           on  h.cgeneralhid = p.cgeneralhid and h.ctrantypeid='1001A210000000002UW7' and h.vdef8='~'
        inner join bd_marbasclass bd
           on bd.code=substr(m.code,0,4)
        where m.creator='1001A210000000000IIQ' 
      order by substr(m.code,0,4)

I searched Stackoverflow, but I can not find the reason of mine:

SQL not a single-group group function

You could find solution for your question [here](https://www.w3schools.com/sql/sql_groupby.asp) — Pham X. Bach, Mar 30 '17 at 09:17
`case when p.vbdef4='~' then '0' else p.vbdef4 end` seems odd. Column p.vbdef4's data type? — jarlh, Mar 30 '17 at 09:17
This seems not so different from [this](http://stackoverflow.com/questions/43112093/ora-00979-not-a-group-by-expression-when-i-execute-my-sql) — Aleksej, Mar 30 '17 at 09:27
Possible duplicate of [ORA-00979: not a GROUP BY expression when I execute my sql](http://stackoverflow.com/questions/43112093/ora-00979-not-a-group-by-expression-when-i-execute-my-sql) — Aleksej, Mar 30 '17 at 09:27
@Aleksej No, i don;t think so, the error information is not the same. — qg_java_17137, Mar 30 '17 at 09:35

score 0 · Accepted Answer · answered Mar 30 '17 at 09:19

0

You should group by some columns:

....
where m.creator='1001A210000000000IIQ'
group by bd.name,
         substr(m.code,0,4)
order by substr(m.code,0,4);

answered Mar 30 '17 at 09:19

Robert Kock

5,795
1
12
20

thank you , your answer is correct, can you tell me why should add `substr(m.code,0,4)` on group by? – qg_java_17137 Mar 30 '17 at 09:21
All selected columns, not involved in a grouping function (sum, max, etc.), should be included in the group-by list. – Robert Kock Mar 30 '17 at 09:24

Not a single-group group function when I execute my SQL

1 Answers1