CODEWITHSUNDEEP
phpmysql
Developer
Developer

Reputation: 39

MySql all rows with sum of one column

I try to create a query to select all rows in a table with sum of one column name newtotal

This is my query

SELECT * 
FROM   tbl_activities
       ,(SELECT SUM(db_newtotal) as total FROM tbl_activities)  {$sql}

{$sql} is where column_name=input from a seach form

But I have this error:

Every derived table must have its own alias

I try to do that: 

SELECT * 
FROM tbl_activities
     ,(SELECT SUM(db_newtotal) as total FROM tbl_activities) as Table {$sql}

But I have this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Table where db_projectname='Barbara Bui'' at line 1

I print the query and this is the output

SELECT * 
FROM tbl_activities
     ,(SELECT SUM(db_newtotal) as total FROM tbl_activities) as Table 
where db_projectname='Barbara Bui'

The output I want should be like this: ll record with projectname name ='Barbara Bui' with the sum the newtotal

projectname   location    Cost
Barbara Bui   verdun      100
Barbara Bui   kaslik      200 
Barbara Bui   achrafieh   500
Total                     800

Query

 SELECT * FROM tbl_activities
        union all
        select db_id,db_category,db_subcategory,db_taskname,db_predecessors,db_unit,db_qty,db_wo,db_duration,db_startdate,db_enddate,db_asd,db_add,db_transferredto,db_prb,db_anotes,db_aduration,db_projectname,db_A,db_AA,db_AAA,db_cost,db_status,db_room,db_floor,db_date,sum(db_newtotal) as total from tbl_activities
       {$sql}

Upvotes: 0

Views: 1622

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

Error is because table is a reserved keyword.

You want to use UNION ALL like this:

select projectname, location, cost
from tbl_activities {$sql}

union all

select 'Total', null, sum(cost)
from tbl_activities {$sql};

Using the parameter to apply the where clause, it becomes:

select projectname,
    location,
    cost
from tbl_activities {$sql}

union all

select 'Total',
    null,
    sum(cost)
from tbl_activities {$sql}

If you have more columns in your first part of the UNION ALL, then make sure to all those many nulls in the second select.

select db_id, db_category, db_subcategory, db_taskname, db_predecessors, db_unit, db_qty, db_wo, db_duration, db_startdate, db_enddate, db_asd, db_add, db_transferredto, db_prb, db_anotes, db_aduration, db_projectname, db_A, db_AA, db_AAA, db_cost, db_status, db_room, db_floor, db_date, db_newtotal
from tbl_activities {$sql}

union all

select null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, sum(db_newtotal)
from tbl_activities {$sql}

Upvotes: 3

rypskar
rypskar

Reputation: 2092

Table is a reserved word in most RDMS so you cannot use it as an alias, try to change to 

SELECT * FROM tbl_activities,(SELECT SUM(db_newtotal) as total FROM tbl_activities) as myTable {$sql}

and see if that works

Upvotes: 0

Related Questions