Mysql group request by name and order by id and limit by id

Question

I have a table like this

id   name   amount
 1   fish    120
 1   meat    230
 2   meat    110
 2   fish    78
 1   salad   50
 3   meat    103
 3   salad    22
 2   salad    34

I want to write a query that will group names and sum their sum, but it should limit the first two identifiers, thus group elements by name and sum each corresponding name ... this should limit it to the first two identifiers, these are only identifiers 1 and 2 leaving id 3. Note. ID is a foreign key from another table.

This is what I tried. but it does not work

select name, sum(amount) from table1 group by amount, id order by id limit 2

this only outputs 2 results after doing the calculation

name   amount
fish   198
meat   443

I expect something like this

name   amount
fish   198
meat   340
salad  84

Add the number of elements with an identifier of only 1 and 2 and group them by name

Answer 1

, , , , .

select t1.name, sum(t1.amount)
from table1 t1 join
     (select tt1.id
      from table1 tt1
      group by id
      order by id
      limit 2
     ) t2
     on t1.id = t2.id
group by t1.name;

:

name    sum(t1.amount)

fish    198

meat    340

salad   84

Answer 2

select name, sum(amount)
from
  (SELECT id, name, amount,
       CASE WHEN @name=name THEN @rown:=@rown+1 ELSE @rown:=0 END as rn,
       @name:=name 
  FROM table1, (select @rown:=0, @name:='') s
  ORDER BY name, id
  ) sub
where rn<=1
group by name

fish    198
meat    340
salad   84

Answer 3

SELECT name, sum(amount) 
  FROM table1 
 WHERE id <= 2
 GROUP BY name
 ORDER BY name

Answer 4

What are the first two identifiers? If I assume that you mean “globally,” then:

select t1.name, sum(t1.amount)
from table1 t1 join
     (select tt1.id
      from table1 tt1
      group by id
      order by id
      limit 2
     ) tt1
     on t1.id = tt1.id
group by t1.name;

Answer 5

Remove LIMITand onlyGROUP BY name

SELECT Name, SUM(amount) AS Amount
FROM table1 
GROUP BY name 
ORDER BY id

Output

Name    Amount
fish    198
meat    443
salad   106

SQL Fiddle: http://sqlfiddle.com/#!9/2a8868/7/0

Answer 6

I think you can use something like this:

SELECT B.NAME, SUM(B.AMOUNT) AS AMOUNT
FROM (
      SELECT a.* ,
      @r:= CASE WHEN @g = a.NAME THEN @r+1 ELSE 1 END consecutive,
      @g:= a.NAME g    
      FROM DISHES a
      CROSS JOIN (SELECT @g:='', @r:=1 ) t1
      ORDER BY A.NAME, A.id
      ) B
      WHERE B.consecutive<=2
      GROUP BY B.NAME;

Conclusion:

NAME    AMOUNT
fish    198
meat    340
salad   84