adding different columns in same table usin inner join in mysql

Question

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.

Abhik Chakraborty · Accepted Answer · 2015-01-23 09:00:14Z

1

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

answered Jan 23, 2015 at 9:00

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Anu Over a year ago

$query = "select p.name as Proj, x.TotalDur, et.details, SUM(et.date_end) as Activitytime from Projects p join ( select project, SUM(date_end) as TotalDur from EmployeeTimeEntry group by project )x on x.project = p.id join EmployeeTimeEntry et on et.project = p.id where p.name = 'Project1' group by p.name,et.details "; my query is like this, but nothing shows in final report.

Anu Over a year ago

My query is working fine with SQLFiddle..may be im missing something in my code.

Abhik Chakraborty Over a year ago

Not quite understand what you said .

Anu Over a year ago

can we assign variable to where clause instead of string like where p.id = et.proj, because im sending this query as a string to another function. i use this same query for displaying all projects. (i.e proj A and proj B).

Abhik Chakraborty Over a year ago

Yes you can use where p.name = 'A' to accept the variable name. This will filter out the result as per the input value.

|

jpw · Accepted Answer · 2015-01-23 08:57:37Z

0

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

answered Jan 23, 2015 at 8:57

jpw

45k6 gold badges71 silver badges90 bronze badges

Collectives™ on Stack Overflow

adding different columns in same table usin inner join in mysql

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related