CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
hemanth
hemanth

Reputation: 189

query to count the number of entries with appears only once in rails activerecord

I want to find out the number of elements which appears only once in a table.

in sql

select count() from (SELECT count(),id FROM trans GROUP BY id HAVING count(*)=1) temptable

How can this be done in rails active record?

there are millions of records in the table.

Upvotes: 1

Views: 1115

Answers (3)

klausinho
klausinho

Reputation: 301

Try 

find_by_sql("SELECT count(*) from ( select id FROM trans GROUP BY id HAVING count(*)=1)")

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

what exectly you want ... ??? sorry but i didn't understand your requirement....

what you have done is good and right one (only one modification is there count(*))...

although you can remove select * from your query...

simply you can write 

SELECT count(*),id FROM trans GROUP BY id HAVING count(*)=1

it will also give the same result with more performance

-- edit / update

as you want to know about the count of this result then you can apply count(*) on this query

as -

  SELECT count(*) from ( select id FROM trans GROUP BY id HAVING count(*)=1)

Upvotes: 2

NARKOZ
NARKOZ

Reputation: 27901

This should work:
Trans.find(:all, :select => 'COUNT(*)', :group => 'trans.id HAVING COUNT(*) = 1')

Upvotes: 1

Related Questions