Retrieve individual values along with their sum?

Question

I have a table that contains link_ids that relate to another table; and this table has column titled ammount that stores values typed int. To retreive some total ammount along with individual values I query it as follows:

SELECT ip, ammount, (SELECT SUM(ammount) FROM stats WHERE link_id = $link_id) as total_ammount FROM stats WHERE link_id = $link_id

Here's values in the stat table:

stat_id, ip, ammount, link_id
1, 211.126.197.45, 10, 3 
2, 61.158.167.84, 7, 3 

So, I need to retrieve the total ammount for link_id along with its individual ammounts:

$total_ammount == 17;
$ips[0]['ammount'] == 10;
$ips[1]['ammount'] == 7;

Something like that... The question is whether the query is alright or it might be better (how to make it better)?

Answer 1

You can use GROUP BY ... WITH ROLLUP:

SELECT stat_id, SUM(ammount) AS amount
FROM stats
WHERE link_id = 3
GROUP BY stat_id WITH ROLLUP

Result:

stat_id  amount
1,       10    
2,       7     
NULL,    17    

Or you can use UNION ALL:

SELECT stat_id, ammount AS amount
FROM stats
WHERE link_id = 3

UNION ALL

SELECT NULL, SUM(ammount)
FROM stats
WHERE link_id = 3