MySQL Join plus Count plus Sum

Question

This is complex query, and I was hoping to achieve it one statement, rather than have to juggle array values in PHP.

To achieve the desired output of:

User    Jobs    Total
John D. 5       $1245.67
Mary L. 3       $800.56

So far, this is the query I have:

SELECT
  SUM(job.cost) AS sum,
  COUNT(DISTINCT job.user) as count,
  user.id, user.firstname, user.lastname
FROM      `job`
LEFT JOIN `user` ON job.user = user.id
GROUP BY user.id

But the count value is wrong: it's the distinct user, so of course each is going to be wrong. How do I fix this?

TABLE user

id, name, etc.

TABLE job

id, user, cost

With ONE user TO MANY job

UPDATE

This seems to be working right:

SELECT 
  SUM(job.cost) AS sum,
  COUNT(1) as count,
  user.id, user.firstname, user.lastname
FROM `job`
LEFT JOIN `user` ON job.user = user.id
GROUP BY user.id

COUNT(DISTINCT job.id) (or any other unique column in job — Wrikken
– Wrikken, Commented Jun 27, 2012 at 21:48
@Wrikken Actually, COUNT(job.user) did it, posting correct (as far as I know) query. — AVProgrammer
– AVProgrammer, Commented Jun 27, 2012 at 21:51

vearutop · Accepted Answer · 2012-06-27 22:20:32Z

2

SET @seq = 0;

SELECT place FROM

(SELECT
  @seq := @seq + 1 AS place
  SUM(job.cost) AS sum,
  COUNT(1) as count,
  user.id, user.firstname, user.lastname
FROM      `job`
LEFT JOIN `user` ON job.user = user.id
GROUP BY user.id
ORDER BY COUNT(1) DESC) 

AS list
WHERE list.id = 'my_user_id'

edited Jun 27, 2012 at 22:20

answered Jun 27, 2012 at 21:50

vearutop

4,14027 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AVProgrammer Over a year ago

How would I determine a specific user's placement in the results without having to loop through them? Any ideas? ---- Actually, I just need to know their placement in regards to number of total jobs

AVProgrammer Over a year ago

Descending order, most jobs to least

Collectives™ on Stack Overflow

MySQL Join plus Count plus Sum

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related