CODEWITHSUNDEEP
mysqlruby-on-railsruby
bjpelcdev
bjpelcdev

Reputation: 303

SQL/Rails return count of unique values from a group of records

I have the following SQL query which extracts the number of unique websites from a table, the count of the number of records for each of the websites and the number of unique users that visited each website.

"SELECT title, COUNT(DISTINCT user_id), COUNT(title) FROM weblogs GROUP BY title"

The table is arranged as follows:

weblog_id | title | user_id
---------------------------
1         | BBC   | 1
2         | BBC   | 1
3         | BBC   | 2
4         | Sky   | 1
5         | Sky   | 1

The above query will return:

title    | COUNT(DISTINCT user_id)   | COUNT(title)
---------------------------------------------------
BBC      | 2                         | 3
Sky      | 1                         | 2

Whilst this works in its current form by executing the SQL string in Rails using exec_query, I have struggled to replicate the same results using Rails' query methods (I'm not a Rails dev but am having to work in Rails). Any ideas on how to build this query up using Rails methods?

EDIT

I can extract the individual total website hit counts and unique users per website using two separate queries. The following gives the count of each website

weblogs.all.group(title).count

And the following gives the count of unique users for each website:

weblogs.all.group(title).distinct.count(user_id)

Is there a way of combining these into one query?

Upvotes: 0

Views: 2245

Answers (1)

Yahor Zhylinski
Yahor Zhylinski

Reputation: 513

this will work:

weblogs.all.group(title).select("COUNT(DISTINCT user_id), COUNT(title), title")

You also can execute your custom sql query Rails 3 execute custom sql query without a model

Upvotes: 3

Related Questions