Arithmetic operation in SQL SELECT statement

Question

here is the sql query that i've tried.

function getall() { 
   $connection = new Connection; 
   $connection->open(); 
   $query = "SELECT position.id,position.name_position,position.g_position,position.cs,position.totalbefore,
            COUNT(position2.staff_id) AS cnt_staff,
            totalbefore-cnt_staff AS totalafter
            FROM position
            INNER JOIN position2 ON position.id = position2.id_position2
            WHERE position.status_data= '1'
            GROUP BY position2.id_position2";
    $result = mysql_query($query);
    return $result; 
}

This code return syntax error. Idk where to put the Arithmetic operation (totalbefore-cnt_staff AS totalafter) in the statement.can anyone help me pls

Check you column name like this "totalbefore-cnt_staff". It may be totalbefore_cnt_staff — Mukesh Kalgude
– Mukesh Kalgude, Commented Feb 11, 2016 at 4:59

artm · Accepted Answer · 2016-02-11 04:58:51Z

1

Use

position.totalbefore - COUNT(position2.staff_id) AS totalafter

You need to calculate the value again

answered Feb 11, 2016 at 4:58

artm

8,5943 gold badges28 silver badges43 bronze badges

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

Comments

Boody · Accepted Answer · 2016-02-11 05:57:27Z

0

Since you have a group by clause in the query, you must use an aggregate function with your arithmetic expression such as

sum(totalbefore-cnt_staff) as totalafter

and the same for the totalbefore column.

anyways, try to put the query in any query editor such as (SQL query editor) and the error messages will be more clear for you.

edited Feb 11, 2016 at 5:57

answered Feb 11, 2016 at 5:52

Boody

1447 bronze badges

Collectives™ on Stack Overflow

Arithmetic operation in SQL SELECT statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related