i have data like this
id | no_nota | item | amount | total_amount |
---|---|---|---|---|
1 | pos-001 | item a | 2500 | |
2 | pos-001 | item b | 3100 | |
3 | pos-001 | item a | 2200 | |
4 | pos-002 | item b | 5200 | |
5 | pos-002 | item a | 3300 | |
6 | pos-003 | item b | 4300 |
and i want result like tis
id | no_nota | item | amount | total_amount |
---|---|---|---|---|
1 | pos-001 | item a | 2500 | 7800 |
2 | pos-001 | item b | 3100 | – |
3 | pos-001 | item a | 2200 | – |
4 | pos-002 | item b | 5200 | 8500 |
5 | pos-002 | item a | 3300 | – |
6 | pos-003 | item b | 4300 | 4300 |
total amount only show in first,pleas help me, thanks
New contributor
mansell Senna is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Join the table with a subquery that gets the lowest ID and sum for each no_nota
value. Using a LEFT JOIN allows you to substitute -
for the rows other than the lowest ID.
<code>SELECT t1.*, COALESCE(t2.total, '-') AS total_amount
FROM yourTable AS t1
LEFT JOIN (
SELECT MIN(id) AS id, SUM(amount) AS total
FROM yourTable
GROUP BY no_nota
) AS t2 ON t1.id = t2.id
ORDER BY t1.id
</code>
<code>SELECT t1.*, COALESCE(t2.total, '-') AS total_amount
FROM yourTable AS t1
LEFT JOIN (
SELECT MIN(id) AS id, SUM(amount) AS total
FROM yourTable
GROUP BY no_nota
) AS t2 ON t1.id = t2.id
ORDER BY t1.id
</code>
SELECT t1.*, COALESCE(t2.total, '-') AS total_amount
FROM yourTable AS t1
LEFT JOIN (
SELECT MIN(id) AS id, SUM(amount) AS total
FROM yourTable
GROUP BY no_nota
) AS t2 ON t1.id = t2.id
ORDER BY t1.id
DEMO
0