2

Can anybody please help me with this particular query? I'm having ORA-00979 when trying to run this:

select   t0.title, count (1) as count0, (select   count (1)
      from contract c1, se se1
      where c1.c_id = se1.c_id
      and se1.svc_id = 3
      and se1.deleted = 0
      and c1.deleted = 0
      and c1.c_date between to_date ('07.10.2000', 'dd.mm.yyyy') 
                        and to_date ('22.11.2010', 'dd.mm.yyyy')
      and c1.company = 0
      and c1.tdata.tariff = c0.tdata.tariff
    ) as count1
  from contract c0, se se0, tariff t0
  where c0.c_id = se0.c_id
  and se0.svc_id = 3
  and se0.deleted = 0
  and c0.deleted = 0
  and c0.c_date between to_date ('21.11.2000', 'dd.mm.yyyy') 
                and to_date ('06.01.2011', 'dd.mm.yyyy')
  and c0.company = 0
  and t0.tariff_id = c0.tdata.tariff
  group by t0.title
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Arino
  • 95
  • 3
  • 9

4 Answers4

3

The problem is your subquery with the select count(1) part. Just because it's got a count in it doesn't actually make it an aggregate. It's still a subquery that will be applied to every row and as you can see it uses the value c0.tdata.tariff which is not part of the group.

Dan
  • 10,990
  • 7
  • 51
  • 80
  • Well said. grouping by c0.tdata.tariff could arrange things. – Benoit Jan 05 '11 at 17:07
  • 1
    @Benoit -- but grouping on that additional column would change the overall query results. Whereas adding a group function, SUM, around the scalar subquery would resolve the issue and total up the counts within the existing groups. – Dave Costa Jan 05 '11 at 18:15
  • Thanks, that is answer for this particular question. But the better solution to the overall problem was presented here http://stackoverflow.com/questions/4605994/oracle-ora-00979-not-a-group-by-expression/4606082#4606082 – Arino Jan 06 '11 at 05:55
1

Looks like that scalar subquery is causing the problem -- it is neither a group function, nor is it in the GROUP BY list.

Probably you could workaround it with something like:

select   t0.title, count (1) as count0, SUM(select   count (1) ...) AS count1
 ...
Dave Costa
  • 47,262
  • 8
  • 56
  • 72
1

Considering this seems to be two instances of the same query just over different dates (I might be wrong here...it's been a long day), you could probably just simplify it and rewrite like this:

select
  t0.title, 
  count (case when c0.c_date between to_date ('21.11.2000', 'dd.mm.yyyy') 
                and to_date ('06.01.2011', 'dd.mm.yyyy') then 1 end) as count0,
  count (case when c0.c_date between to_date ('07.10.2000', 'dd.mm.yyyy') 
                and to_date ('22.11.2011', 'dd.mm.yyyy') then 1 end) as count1
from 
  contract c0, 
  se se0, 
  tariff t0
where 
  c0.c_id = se0.c_id
  and se0.svc_id = 3
  and se0.deleted = 0
  and c0.deleted = 0
  and (c0.c_date between to_date ('21.11.2000', 'dd.mm.yyyy') 
                and to_date ('06.01.2011', 'dd.mm.yyyy')
    or c0.c_date between to_date ('07.10.2000', 'dd.mm.yyyy') 
                        and to_date ('22.11.2010', 'dd.mm.yyyy'))
  and c0.company = 0
  and t0.tariff_id = c0.tdata.tariff
group by t0.title
Mike Meyers
  • 2,885
  • 1
  • 20
  • 26
  • That is great solution, thank you! I did not know it is possible to solve the problem like that, it is also solves "zeroes" problem. – Arino Jan 06 '11 at 05:57
0

Your group by needs to include all of the non-aggregate columns from your select list. In this case, the group by is missing the count1 returned by your subquery.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235