MySQL: How can I use SQL_CALC_FOUND_ROWS with LIMIT and at the same get the SUM of a column?

So SQL_CALC_FOUND_ROWS can be used together with LIMIT to get all items needed. But my problem is that I also want to get the SUM of a column of all those items. I tried the following:

因此,SQL_CALC_FOUND_ROWS可与LIMIT一起使用以获取所需的所有项目。但我的问题是我还想获得所有这些项目的列的SUM。我尝试了以下方法:

"SELECT SQL_CALC_FOUND_ROWS *, SUM('score') FROM `tab` WHERE 1
LIMIT 1, 10"

Interestingly, it returns only one item instead of 10 as it is supposed to. I am pretty sure it is caused by the SUM function as when I remove the SUM function, it returns 10 items correctly. So how do I achieve what I want and why the above one is not working?

有趣的是,它只返回一个项而不是它应该的10个。我很确定它是由SUM函数引起的,因为当我删除SUM函数时,它会正确返回10个项目。那么我如何实现我想要的以及为什么上面的那个不起作用?

To be concise, take the following table as an example:

简而言之,以下表为例:

id name score
1  abc  10
2  def  20
3  abc  30

I would like to return the following results:

我想返回以下结果:

id name score total_score
1  abc  10    40
2  def  20    20
3  abc  10    40

Another related question is: I know that total_score is repeating itself for the same user. So the related question is: Is using one query (if it is possible) better than two queries in this case?

另一个相关问题是:我知道total_score正在为同一个用户重复。所以相关的问题是:在这种情况下,使用一个查询(如果可能)比两个查询更好吗?

1 个解决方案

#1

You will need to use the GROUP BY clause along with using an aggregation function like SUM.

您将需要使用GROUP BY子句以及使用SUM之类的聚合函数。

For example, if your structure was something like this:

例如,如果您的结构是这样的:

create table tab (
  id int auto_increment primary key,
  gamename varchar(20),
  score int
);

insert into tab (gamename, score) values 
('game1', 20),
('game2', 30),
('game3', 40),
('game2', 60);

Your query to extract the sum would look like this:

提取总和的查询将如下所示:

SELECT SQL_CALC_FOUND_ROWS gamename, SUM(score) 
FROM `tab` 
group by gamename
LIMIT 1, 10;

-- Results
gamename  sum(score)
--------  ----------
game2     90
game3     40

select found_rows();
found_rows()
------------
3

Hope this helps out.

希望这会有所帮助。

Corresponding edit to the question

相应的编辑问题

select SQL_CALC_FOUND_ROWS a.*, b.totals
from tab a 
inner join (
    select gamename, sum(score) as totals
    from tab 
    group by gamename
) b on a.gamename = b.gamename
limit 0, 2;

-- Result
id  gamename  score  totals
--  --------  -----  ------
1   game1     20     20
2   game2     30     90

select found_rows();
found_rows()
------------
4

#1