0

Oracle 10g ,the sql throws exception:"not a group by expression"

select count(*)
        from (
            select h.personal_info_id pid,h.hbsag hbsag,h.sgpt sgpt,MAX(h.date_reported)
            from health_checkup_info h
            inner join personal_info p on h.personal_info_id = p.id
            where
                h.deleted = 0
                and h.date_reported is not null
                and h.hbsag in(1,2)
                and p.deleted = 0

            group by h.personal_info_id
        ) t where t.hbsag=1 and t.sgpt>=20

Then, I changed the 'group by' params, add 'h.hbsag' 'h.sgpt', like: 

group by h.personal_info_id,h.hbsag,h.sgpt

But the result is not correct.

sfxm
  • 131
  • 1
  • 5

3 Answers3

1

Thanks everyone,now I have already solved the problem.The query is:

select count(*)
        from (
            select h.personal_info_id pid,h.hbsag,ROW_NUMBER() OVER (partition by h.personal_info_id order by h.date_reported desc) r
            from health_checkup_info h
            inner join personal_info p on h.personal_info_id = p.id
            where
                h.deleted = 0
                and h.date_reported is not null
                and h.hbsag in(1,2)
                and p.deleted = 0

        ) t where t.hbsag=2 and r=1
sfxm
  • 131
  • 1
  • 5
0

You should add all the columns that are not part of aggregate functions. Try this.

select count(*) from 
( 
    select h.personal_info_id pid,h.hbsag hbsag,h.sgpt sgpt,MAX(h.date_reported) 
    from health_checkup_info h inner join personal_info p on h.personal_info_id = p.id 
    where h.deleted = 0 and h.date_reported is not null and h.hbsag in(1,2) and p.deleted = 0
    group by h.personal_info_id ,h.hbsag ,h.sgpt 
) t
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
0

Its Oracle's behavior, all selected columns except aggregate function must be grouped by.

select count(*)
        from (
            select h.personal_info_id pid,h.hbsag hbsag,h.sgpt sgpt,MAX(h.date_reported)
            from health_checkup_info h
            inner join personal_info p on h.personal_info_id = p.id
            where
                h.deleted = 0
                and h.date_reported is not null
                and h.hbsag in(1,2)
                and p.deleted = 0

            group by h.personal_info_id,h.hbsag,h.sgpt
        ) t

Update:

As you are just using the count, What is need to fetch other columns?? try this..

select count(*)
        from (
            select h.personal_info_id pid,MAX(h.date_reported)
            from health_checkup_info h
            inner join personal_info p on h.personal_info_id = p.id
            where
                h.deleted = 0
                and h.date_reported is not null
                and h.hbsag in(1,2)
                and p.deleted = 0

            group by h.personal_info_id
        ) t;
manurajhada
  • 5,284
  • 3
  • 24
  • 43
  • You don't need to specify column alias in the group by clause. I think it is a copy/paste thing :) – Madhivanan Jul 11 '12 at 07:53
  • @manurajhada,Thank you! But if use 'group by h.personal_info_id,h.hbsag,h.sgpt', the result is not correct. For example,use 'group by h.personal_info_id',the result is 2640.However,use 'group by h.personal_info_id,h.hbsag,h.sgpt',the result is 2641.Why? – sfxm Jul 11 '12 at 08:06
  • yeah you are right let me update the query, just an another logic to achieve your goal.. – manurajhada Jul 11 '12 at 08:09
  • @manurajhada No,no,no.I need the h.hbsag,h.sgpt.and use it again filtered. " group by h.personal_info_id ) t where h.hbsag=1 and h.sgpt>=20 " – sfxm Jul 11 '12 at 08:18
  • what is your absolute requirement?? – manurajhada Jul 11 '12 at 08:20
  • @manurajhada For example: select count(*) from ( select h.personal_info_id,h.hbsag hbsag,h.sgpt sgpt, MAX(h.date_reported) from health_checkup_info h inner join personal_info p on h.personal_info_id = p.id where h.deleted = 0 and h.date_reported is not null and h.hbsag in(1,2) and p.deleted = 0 and h.sgpt>=0 group by h.personal_info_id ) t where t.hbsag=2 and t.sgpt>=20.0 – sfxm Jul 11 '12 at 08:24
  • Use this query... ` select count(*) from ( select h.personal_info_id,MAX(h.date_reported) from health_checkup_info h inner join personal_info p on h.personal_info_id = p.id where h.deleted = 0 and h.date_reported is not null and t.hbsag=2 and p.deleted = 0 and t.sgpt>=20.0 group by h.personal_info_id ) t ` – manurajhada Jul 11 '12 at 08:48