Retrieve individual values along with their sum?
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)?
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
精彩评论