Question 177819 in Mysql Question posted in
The official MySQL documentation can be found here.

Can I group & sum data from a PHP & MySQL column?

I want to generate report in phpmysql in such a way that first detail rows display and in the bottom , sum of that detail row display on the basis of group by statement.

Select id, 
       Emp-name,  
       department, 
       salary, 
       sum(salary) as total sum for each dept
from Employee 
group by department

I tried the following  thing
Select id, 
       Emp-name,  
       department, 
       salary, 
       (select sum(salary) from Employee) as total sum for each dept
from Employee 
group by department

desired out put :

Employee Name Department Salary
Umar IT 50000
ali IT 40000
sub total IT 90000
waseem HR 25000
waqar HR 15000
sub total HR 40000
Tags:

2

Answers


  1. 0

    Your query is grouping by department which will give the total per department and the name and salary of the first employee (which is useless to you here).
    You would need to do a separate query to get totals per dept, but in this case it’s better to just order by dept and then do a subtotal per dept when the dept changes. e.g. (pseudo code)

    while ($row = fetcharray($result))
{
   if ($row['dept'] != $dept)
   {   if ($dept)
       {   // do subtotal
           $dept_salary = 0;
       }
       $dept = $row['dept'];
   }
   // do employee row
   $dept_salary += $row['salary'];
}
    Login or Signup to reply.
  2. 0

    Here’s what you are looking for:

    select emp_name, department, salary from employees
       UNION ALL
       select 'sub-total' emp_name, department department,  sum(salary) salary from employees GROUP BY department;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search