The difference between WHERE and HAVING is that WHERE is applied before aggregate functions are computed, and HAVING is applied after aggregate fucntions are computed.
SELECT COUNT (create_time) FROM comm_voice_extra GROUP BY create_time HAVING COUNT (create_time)> 1 ;
949523 wrote:Remember the ABC's of GROUP BY:
yes, Having is correct one
select count(create_time) from comm_voice_extra where count(create_time)> 1 group by create_time;
now I get the following
count (create-time) 2,
if I want to see the data
the following seems not working .......
select *,count(create_time) from comm_voice_extra where count(create_time)> 1 group by create_time;
it gives ORA-00923: FROM keyword not found where expected.........
WITH got_cnt AS ( SELECT comm_extra_vocie.* , COUNT (create_time) OVER (PARTITION BY create_time) AS total_cnt FROM comm_voice_extra ) SELECT * FROM got_cnt WHERE total_cnt > 1 ;