mardi 28 juin 2016

Ratio or Percentage from group by SQL query from column with condition and without condition

I am having some trouble with a SQL query. From a table let's call it Reports:

I want to group all the reports by the name column.

Then for each of those name groups I want to go to the rating column and count the number of times the rating was 15 or less. Let's say this happened 10 times for one of the groups with the name BOBBO.

I also want to know the number of times ratings were submitted (same as total number of records for each name group). So using the name group BOBBO let's say he has 20 ratings.

So under the condition the group BOBBO 50% of the time has a rating 15 or less.

I've seen these posts -- I am still having some trouble cracking this.

using-count-and-return-percentage-against-sum-of-records

getting-two-counts-and-then-dividing-them

getting-a-percentage-from-mysql-with-a-group-by-condition-and-precision

divide-two-counts-from-one-select

After reading those I tried queries like these:

    ActiveRecord::Base.connection.execute
("SELECT COUNT(*) Matched,
     (select COUNT(rating) from reports group by name) Total,
     CAST(COUNT(*) AS FLOAT)/CAST((SELECT COUNT(*) FROM reports group by name) AS FLOAT)*100 Percentage from reports
    where rating <= 15 order by Percentage")


    ActiveRecord::Base.connection.execute
("select name, sum(rating) / count(rating) as bad_rating
 from reports group by name having bad_rating <= 15")

Any help would be very much appreciated!

Aucun commentaire:

Enregistrer un commentaire