CODEWITHSUNDEEP
mysql
Anu
Anu

Reputation: 925

adding different columns in same table usin inner join in mysql

I want to make a report of time entry of particular projects. I tried below query.

Table1: Projects

id   | Name
------------
1    |  A
2    |  B

Table2: EmployeeTimeEntry

proj | activity  |time
----------------------
1    | coding    | 5
2    | coding    | 2
1    | testing   | 2
1    | coding    | 2

My desired Outpput for proj A:

proj  | TotalDur  | activity | Activitytime
--------------------------------------------
A     |  9        | coding   |  7
A     |  9        | testing  |  2

My Query :

$query = "SELECT        
    name  as 'Proj',        
    TimeEntry.Total as 'TotalDur',
    ATimeEntry.ADetails as 'activity',
    ATimeEntry.ATotal as 'Activitytime'             
    FROM Projects pr

    INNER JOIN(SELECT project,SUM(time) as Total from EmployeeTimeEntry group by project ) TimeEntry on pr.id = TimeEntry.project
    INNER JOIN(SELECT project,details as ADetails,SUM(time) as ATotal from EmployeeTimeEntry where id = pr.id group by details ) ATimeEntry on pr.id = TimeEntry.project";

But i got output as

proj  | TotalDur  | activity | Activitytime
--------------------------------------------
A     |  9        | coding   |  9
A     |  9        | testing  |  2

All activity times for all projects get added . I use combobo to select which projects to show the report.

Upvotes: 1

Views: 112

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

I think you are over complicating it 

select
p.name as Proj,
x.TotalDur,
et.activity,
sum(et.time) as Activitytime
from Projects p
join (
   select proj, sum(time) as TotalDur from EmployeeTimeEntry group by proj
)x on x.proj = p.id
join EmployeeTimeEntry et on et.proj = p.id
where p.name = 'A'
group by p.name,et.activity

DEMO

Upvotes: 1

jpw
jpw

Reputation: 44891

Maybe this is what you want?

select 
    p.Name as Proj, 
    (select sum(time) as TotalDur from EmployeeTimeEntry where proj = p.id group by proj) TotalDur, 
    activity, 
    sum(e.time) as ActivityTime 
from Projects p
inner join EmployeeTimeEntry e on e.proj = p.id
where p.Name = 'A'
group by name, activity, p.id

Sample SQL Fiddle

Upvotes: 0

Related Questions