CODEWITHSUNDEEP
mysqlsql
user2925000
user2925000

Reputation: 13

SQL Query problems

I have a table named userRatings with a field called userNumber (int), userRating (int), and userRatingDateTime (datetime).

I have a second table named userProfiles with a field called userNumber (int), userGender (int).

What I'm trying to do is this:

I want to query all userNumbers along with the total sum of their userRating, where userGender = 1 (represents female), but only in the last week. (In other words, ratings from beyond one week ago should not be totaled.)

I have tried many different approaches to this problem but I never seem to get it right. Currently my syntax involves nesting one SELECT in another, but it's not yielding what I want - not even close - so I won't bother sharing it.

Can someone with a little more SQL experience help me with this one? Thanks.

Upvotes: 1

Views: 55

Answers (1)

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

Select
ur.userNumber,
SUM(userRating) as totalRating,
userGender
from userRatings ur
inner join userProfiles up on (ur.userNumber  = up.userNumber )
where up.userGender=1
AND userRatingDateTime<DATEDIFF(now() INTERVAL 7 DAYS)

 GROUP BY  ur.userNumber

Upvotes: 1

Related Questions