Query Math

[ggggus]

I need to do a query from three different tables each containing information about a user. count the returns, do math on each return, and add them together to produce a score. Can I do this in one query?

SELECT users.user_id, users.user_name, Count(objects.object_id) as num_objects
FROM users, objects
WHERE users.user_id=objects.upload_user_id
GROUP BY user_id
ORDER BY num_objects DESC

here's one query.
I want to count the number of objects, images, and posts uploaded, and give them each points, 3 points for objects, 2 for images, and 1 for posts.

Can I count all of these and do the math in the query?

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[sleipnir214]

Have you tried it?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[swampBoogie]

SELECT users.user_id, users.user_name, 
sum(case object_type 
         when 'OBJECT' then 3
         when 'IMAGE' then 2
         when 'POSTS' then 1 
         else 0 end) as score
FROM users, objects
WHERE users.user_id=objects.upload_user_id
GROUP BY users.user_id, users.user_name
ORDER BY score DESC

 

[ggggus]

I guess I didn't explain myself enough, sorry about that...

Ok, so I have 4 tables: objects, images, messages, and users.

the objects, images, and messages tables have the field upload_user_id which reflect the user_id of the person who wrote or uploaded the item.

I'm trying to figure out the most contributing members of the site, weighting each action differently.

I tried this but I get the error:
Invalid use of group function.

SELECT users.user_id, users.user_name, SUM(Count(families.family_id)*10+ Count(gallery.upload_user_id)*2+ Count(messages.user_id)) as score FROM users, families, gallery, messages
WHERE users.user_id=families.upload_user_id
AND users.user_id=gallery.upload_user_id
AND users.user_id=messages.user_id
GROUP BY user_id
ORDER BY score DESC
LIMIT 0,10

 

[ggggus]

Oh, the objects are called families, and the images are in the gallery, so this query here is the actual query with correct table names...sorry if I'm confusing the issue there.