MySQL Join plus Count plus Sum

问题描述：

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

这是复杂的查询，我希望能够实现一个语句，而不必在PHP中处理数组值。 p>

要获得所需的输出： p>

 用户工作总数
John D. 5 $ 1245.67 
Mary L. 3 $ 800.56 
   code>  pre> 
 
 到目前为止，这是我的查询： p> 
 
 
  SELECT 
 SUM（job.cost）AS sum  ，
 COUNT（DISTINCT job.user）作为计数，
 user.id，user.firstname，user.lastname 
FROM`work` 
LEFT JOIN`user` ON job.user = user.id 
GROUP BY user。  id 
  code>  pre> 
 
 但是计数值是错误的：它是不同的用户，所以当然每个都是错的。 我该如何解决这个问题？ p> 
 
 
 TABLE  user  code>  p> 
 
 
  id，name等
  代码>  pre> 
 
  TABLE  job  code>  p> 
 
 
  id，user，cost 
  code>  pre  > 
 
 使用一个 user  code> TO MANY  job  code>  p> 
 
 

 UPDATE  p  > 
 
 
这似乎工作正常： p> 
 
 
  SELECT 
 SUM（job.cost）AS sum，
 COUNT（1）作为计数，  
 user.id，user.firstname，user.lastname 
FROM`work` 
LEFT JOIN`user` ON job.user = user.id 
GROUP BY user.id 
  code>  pre> 
   DIV>

答

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'

相关推荐