SQL help

Tom Dunne

Ok, coming from an Oracle background and I am doing what I thought would be relatively simple task in MySQL.

What I have are two tables, with a common field.

Table A: Project_Id, hours (and others)
Table B: Project_Id, budgeted_hours (and others)

I am trying to do a simple

SELECT sum(tableA.hours), sum(tableB.budgeted_hours)
FROM TableA, TableB
WHERE TableA.Project_ID=320
AND TableA.Project_ID=TableB.Project_ID

And it's not giving me what I expected. Say I have 10 rows in TableA and 20 rows in TableB, it's giving me the sum of all 200 rows for each table.

Am I missing something obvious? This has my head all messed up.

FindingNemo

tom dunne wrote: »

Ok, coming from an Oracle background and I am doing what I thought would be relatively simple task in MySQL.

What I have are two tables, with a common field.

Table A: Project_Id, hours (and others)
Table B: Project_Id, budgeted_hours (and others)

I am trying to do a simple

SELECT sum(tableA.hours), sum(tableB.budgeted_hours)
FROM TableA, TableB
WHERE TableA.Project_ID=320
AND TableA.Project_ID=TableB.Project_ID

And it's not giving me what I expected. Say I have 10 rows in TableA and 20 rows in TableB, it's giving me the sum of all 200 rows for each table.

Am I missing something obvious? This has my head all messed up.

have you tried adding in a GROUP BY clause

Tom Dunne

FindingNemo wrote: »

have you tried adding in a GROUP BY clause

I sure have. Grouped by TableA.Project_id, TableB.project_id which you would assume would work.

Ginger

Its because it looks like its doing a cross join, rather than an inner/outer join I think.

Specify either an inner join or outer join depending on your requirements

FindingNemo

tom dunne wrote: »

I sure have. Grouped by TableA.Project_id, TableB.project_id which you would assume would work.

can you post your full statement up with the group clause please.
You'll definetely need that, also you can't group it by TableA.Project_id, TableB.project_id.
When you do select sum(column), you should use select sum(column) as TEST and then group it by TEST

Tom Dunne

Ginger wrote: »

Its because it looks like its doing a cross join, rather than an inner/outer join I think.

Specify either an inner join or outer join depending on your requirements

And therein lies my problem, I think. I don't know how to specify what I want.

Table A has 9 rows (where project_id=X)
Table B has 813 rows (where project_id=X)

Where X is the same in both tables.

I want the sum of all 9 rows in table A and I want the sum of all 813 rows in table B, where both of them have a common project_ID.

So the end result two numbers, the sum from each table.

Tom Dunne

FindingNemo wrote: »

can you post your full statement up with the group clause please.
You'll definetely need that, also you can't group it by TableA.Project_id, TableB.project_id.
When you do select sum(column), you should use select sum(column) as TEST and then group it by TEST

select sum(bud.budtime) as sum_bud, sum(tim.timespent) as sum_spent
from mis_buditems as bud, mis_timeitems as tim
where bud.project=tim.project
and bud.project=349
group by bud.project, tim.project

FindingNemo

tom dunne wrote: »

select sum(bud.budtime) as sum_bud, sum(tim.timespent) as sum_spent
from mis_buditems as bud, mis_timeitems as tim
where bud.project=tim.project
and bud.project=349
group by bud.project, tim.project

just at a glance here, you have to group by your columns in your select statement, not your where clause,
so the group by clause should be
group by sum_bud, sum_spent

Ginger

Does mySQL support the Having keyword?

FindingNemo

Ginger wrote: »

Does mySQL support the Having keyword?

it sure does ginger, only recently though!

Tom Dunne

FindingNemo wrote: »

just at a glance here, you have to group by your columns in your select statement, not your where clause,
so the group by clause should be
group by sum_bud, sum_spent

Ginger wrote: »

Does mySQL support the Having keyword?

Right, here's what worked:

select bud.project,bud.budsum, tim.timsum
from (
select project, sum(budtime) as budsum
from mis_buditems
group by project
)as bud inner join (
select project,sum(timespent) as timsum
from mis_timeitems
group by project
) as tim on bud.project = tim.project

Edit: I should point out that code was thanks to my co-worker not me