CODEWITHSUNDEEP
grails
Hamila Mohamed Amine
Hamila Mohamed Amine

Reputation: 290

grails query to get the average of each day of the week

I have a grails domain class which looks something like this : 

class Answer {
String response
Integer score
Date created = new Date()
}

how can i make a query that returns me the average score for each day of the week ? , like this :

average for Mondays = 25
average for Tuesdays = 20
....
average for sundays = 10

Upvotes: 0

Views: 488

Answers (3)

dmahapatro
dmahapatro

Reputation: 50245

This is addition to @lucke84's answer, a small modification to his approach (in case my edit to his answer was missed)

def summation = [
'monday': [],
'tuesday': [], 
'wednesday': [],
'thursday': [],
'friday': [],
'saturday': [],
'sunday': []
]


def answers = Answer.list() 
answers.each {
    def dateCreated = it.created[Calendar.DAY_OF_WEEK]

    switch (dateCreated) {
        case Calendar.MONDAY:
            summation.monday << it.score     
            break
        case Calendar.TUESDAY:
            summation.tuesday << it.score
            break
        case Calendar.WEDNESDAY:
            summation.wednesday << it.score
            break
        case Calendar.THURSDAY:
            summation.thursday << it.score
            break
        case Calendar.FRIDAY:
            summation.friday << it.score   
            break
        case Calendar.SATURDAY:
            summation.saturday << it.score
            break
        case Calendar.SUNDAY:
            summation.sunday << it.score
            break
    } ​ 
}

def averages = [:]
averages.monday = (summation['monday'].sum())?.divide(summation['monday'].size())
//So on and So forth.

Keep a note that .list() would bring back all the rows from Answer which might affect performance. In that case you can use findAll and pass in pagination parameters according to your need.

Upvotes: 1

Shafiul
Shafiul

Reputation: 1512

To get sunday's avg, first get date of sunday. and then use criteria query to find avg. See example below:

dateOfSunday = ..
def c = Answer.createCriteria()
def avgScore = c.get {
  projections {
    avg "score"
  }
 lt("created",dateOfSunday.plus(1) )
 gt("created",dateOfSunday.minus(1) )
}

Hope you got the idea...

EDIT:

Ok, If you want all sunday's avg, then you can use formula in your domain class, like:

class Answer {
 String response
 Integer score
 Date created = new Date()
 static mapping = {
    dayOfWeek formula: 'DAYOFWEEK(created)'
   }
}

And then query would be:

def c = Answer.createCriteria()
def avgScore = c.get {
  projections {
    avg "score"
  }
 eq("dayOfWeek ",7)
}

EDIT:

@dmahapatro, Thanks for pointing it out.

All avg scores also can be get using single db hit. See below:

def results = Answer.createCriteria().list() {
  projections {
    groupProperty('dayOfWeek')
    avg "score"
  }
}

Though it involves less code, but it has a drawback, it is using db function which will make the query slow. You can use the suggestion by dmahapatro.

Upvotes: 2

lucke84
lucke84

Reputation: 4636

I'm not really sure I've got your question and your domain model, but maybe this draft could be helpful.

def summations = [
    'monday': [],
    'tuesday': [], 
    'wednesday': [],
    'thursday': [],
    'friday': [],
    'saturday': [],
    'sunday': []
]

def answers = Answer.list() 
answers.each {
    def dateCreated = it.created[Calendar.DAY_OF_WEEK]

    switch (dateCreated) {
        case Calendar.MONDAY:
            summations['monday'] << it.score     
            break
        case Calendar.TUESDAY:
            summations['tuesday'] << it.score
            break
        case Calendar.WEDNESDAY:
            summations['wednesday'] << it.score
            break
        case Calendar.THURSDAY:
            summations['thursday'] << it.score
            break
        case Calendar.FRIDAY:
            summations['friday'] << it.score   
            break
        case Calendar.SATURDAY:
            summations['saturday'] << it.score
            break
        case Calendar.SUNDAY:
            summations['sunday'] << it.score
            break
    } ​ 
}

// you can now do the average for every list in the map (sum of elements divided by number of item)

EDIT: This is from @dmahapatro:

def averages = [:]
averages.monday = (summations['monday'].sum())?.divide(summations['monday'].size())
// So on and So forth.

Upvotes: 1

Related Questions