summing two colums total in mysql

Question

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?

Answer 1

SELECT SUM(Number_of_posts), SUM(Number_of_user) FROM table;

Answer 2

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.)

Answer 3

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

Answer 4

SELECT  id, number_of_posts, number_of_user,
        (
        SELECT  SUM(number_of_posts + number_of_user)
        FROM    mytable
        )
FROM    mytable

Answer 5

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.

Answer 6

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. =)