Hi and good day everyone, i'm here to ask currently i'm trying to get total quantity as expect below table. The total quantity will auto calculate the total quantity group by data column until the data is changed to others data
Previous Result:
Id Name Data
----------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1
Expected Result:
Id Name Data Total Quantity
-----------------------------------------
1 A A_data_1 null
2 A A_data_2 null
3 A A_data_3 3
4 B B_data_1 null
5 B B_data_2 2
6 C C_data_1 1
I have try to use group by but it seems not working for me
CodePudding user response：
For the expected result you need to use left join with the subquery which get the total count for each group.
Try:
select test.id,test.name,test.`data`,total_count
from test
left join ( select max(id) as id ,name,count(name) as total_count
from test
group by name
) as t1 on t1.id=test.id;