1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Get Sum value at the end of column in mysql

Discussion in 'MySQL' started by dbyoung1000, Jan 7, 2013.

  1. #1
    Hi,

    Iam new to Mysql.I created a table as given below.I want to show the sum of the column values at the bottom of the table (i.e.at Last Row) as Total.
    Please tell me the query for that.

    create table employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name varchar(20),
    dept varchar(10),
    site varchar(20),
    dailywage int(10),
    dailyallowance int(10),
    dailyexpense int(10)
    );


    insert into employee values(100,'Ben','Sales','ada',200,100,50);
    insert into employee values(200,'Jack','Technology','ann',200,100,50);
    insert into employee values(300,'Matt','Technology','nam',200,100,50);
    insert into employee values(400,'Nami','Marketing','man',200,100,50);
    insert into employee values(500,'Randy','Technology','kuk',200,100,50);


    +-----+---------+------------+--------------+-----------+----------------+--------------+
    | id | name | dept | site | dailywage | dailyallowance | dailyexpense |
    +-----+---------+------------+--------------+-----------+----------------+--------------+
    | 100 | Ben | Sales | ada | 200 | 100 | 50 |
    | 200 | Jack | Technology | ann | 200 | 100 | 50 |
    | 300 | Matt | Technology | nam | 200 | 100 | 50 |
    | 400 | Nami | Marketing | man | 200 | 100 | 50 |
    | 500 | Randy | Technology | kuk | 200 | 100 | 50 |
    +-----+---------+------------+--------------+-----------+----------------+--------------+
    Total: 1000.00 500.00 250.00

    Help is appreciated.
     
    dbyoung1000, Jan 7, 2013 IP
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    Select sum(dailywage), sum(dailyallowance), sum(dailyexpense), id, name, dept, site from employee group by id, name, dept, site;
     
    Rukbat, Jan 9, 2013 IP
  3. amrush

    amrush Active Member

    Messages:
    30
    Likes Received:
    2
    Best Answers:
    2
    Trophy Points:
    58
    #3
    You can try this query,

    SELECT
        name,
        dept,
        site,
        IFNULL(id, 'TOTAL') AS id,
        SUM(dailywage) AS WAGE,
        SUM(dailyallowance) AS ALLOWANCE,
        SUM(dailyexpense) AS EXPENSE
    FROM employee
    GROUP BY id WITH ROLLUP;
    Code (markup):
    regards,

    --amrush
     
    amrush, Jan 22, 2013 IP