I want a resultset for this table:
ID Number_of_posts Number_of_user
1 100 21
2 23 34
as
ID Number_of_posts Number_of_user Number_of_posts_AND_Number_of_user
1 100 21 178
2 23 34 178
-----------------------------------------------
123 55
Is it possible to get the sum of two colums as another column/ as output in mysql?
To get cross-tab totals (horizontal and vertical):
select id,
number_of_posts as p,
number_of_users as u,
number_of_posts+number_of_users as p_and_u
from tbl
union all
select 99999 as id,
sum(number_of_posts) as p,
sum(number_of_users) as u,
sum(number_of_posts+number_of_users) as p_and_u
from tbl
order by 1
This will give you:
id p u p_and_u
----- --- --- -------
1 100 21 121
2 23 34 57
99999 123 55 178
You're complicating your query needlessly and using more memory that you have to. Pull the records in one query, then make another query to get the aggregates.
I know it doesn't answer your question, but it's what you should be doing instead. =)
SELECT id, number_of_posts, number_of_user,
(
SELECT SUM(number_of_posts + number_of_user)
FROM mytable
)
FROM mytable
SELECT SUM(Number_of_posts), SUM(Number_of_user) FROM table;
SELECT *,
(SELECT SUM(Number_of_posts) + SUM(Number_of_user) FROM TABLE) AS total
FROM table;
(Edit: Didn't originally notice it was the total total in the last column.)
Does MySQL support ROLLUP?
SELECT id,
SUM(Number_of_posts) Number_of_posts,
SUM(Number_of_user) Number_of_user,
SUM(Number_of_posts) + SUM(Number_of_user) Number_of_posts_AND_Number_of_user
FROM table
GROUP BY ROLLUP(id)
Edit: based on a quick search, in MySQL the last line might be GROUP BY id WITH ROLLUP.
