mhmd mhmd - 2 months ago 4
MySQL Question

select all from two tables with sum cost

I try to select all from 2 tables in mysql database with the sum of total cost in the second table
i have a table name

tbl_project
contain

db_id db_projectname
and other column

1 test
2 test2
3 test3


second table name
tbl_activities
contain

db_id db_projectname db_totalcost
1 test 200
2 test 300
3 test2 800


the out put i want is

test 500
test2 800
test3


i try this query but it didn't give me that result

select tbl_project.db_id, tbl_project.db_projectname,tbl_project.db_location,tbl_project.db_client,tbl_project.db_transferredto,tbl_project.db_psd,tbl_project.db_pdd,tbl_project.db_duration,tbl_project.db_past,tbl_project.db_padd,tbl_project.db_aduration,tbl_project.db_percent,tbl_project.db_pnote,tbl_project.db_user,tbl_project.db_cpercentage,tbl_project.db_epercentage,tbl_project.db_mpercentage,tbl_project.db_status,tbl_project.db_offer,tbl_project.db_sheet,tbl_project.db_invoice,tbl_project.db_po,sum(tbl_activities.db_totalcost) as total_cost from tbl_project,tbl_activities where
tbl_project.db_projectname=tbl_activities.db_projectname


it give me
test but sum of another project and only one project not all

Answer

You needed to use LEFT JOIN & GROUP BY

SELECT
    tbl_project.db_id,
    tbl_project.db_projectname,
    tbl_project.db_location,
    tbl_project.db_client,
    tbl_project.db_transferredto,
    tbl_project.db_psd,
    tbl_project.db_pdd,
    tbl_project.db_duration,
    tbl_project.db_past,
    tbl_project.db_padd,
    tbl_project.db_aduration,
    tbl_project.db_percent,
    tbl_project.db_pnote,
    tbl_project.db_user,
    tbl_project.db_cpercentage,
    tbl_project.db_epercentage,
    tbl_project.db_mpercentage,
    tbl_project.db_status,
    tbl_project.db_offer,
    tbl_project.db_sheet,
    tbl_project.db_invoice,
    tbl_project.db_po,
    sum(
        tbl_activities.db_totalcost
    ) AS total_cost
FROM
    tbl_project
LEFT JOIN tbl_activities ON tbl_project.db_projectname = tbl_activities.db_projectname
GROUP BY tbl_project.db_id

Note:

Using aggregate function (e.g. SUM,COUNT..) without GROUP BY collapses the result set into a single row.

Comments