View Full Version : Select column AND sum()

Ted Varnson
09-21-2006, 06:05 PM
Is it possible to query the database and get the sum() of column a and still get information as it loops from column b,c,d etc?

Perhaps I am overlooking something. Here is my query.

mysql_query('SELECT SUM(j.time_hours*e.employee_wage) AS "total" FROM `employee_timecard` t
INNER JOIN `employee` e ON e.employee_id=t.time_employee_id
INNER JOIN `timecard_jobcode` j ON j.time_id=t.time_id
WHERE t.time_job_id = ' . $_GET['job'] . ' AND j.time_code = ' . $classid . ' AND t.time_day >= ' . $_GET['start'] . ' AND t.time_day <= ' . $_GET['end'] . '

09-21-2006, 07:01 PM
You will be better served with 2 queries because SUM is an aggregate function. It takes the rows and combines them into one. If you add another column to the select, you'll have to group by that column, and then you won't get the sum of the entire column of a, you'll only get the sum of the a column grouped by values in the b column.

SELECT SUM(a), b from table1 group by b

So if the b column has 10 values of 5 and 10 values of 6, and the a column has 20 values of 5, you'll get:

a ___ b
50 ___ 5
50 ___ 6

which isn't what you want.

So yeah, you're better off with 2 queries.

Ted Varnson
09-21-2006, 07:04 PM
ok, thanks a lot man! :)

09-21-2006, 07:21 PM
your query and your question do not match up. you asked about a sum of one column but information on three other columns. you are only querying a single column in your select statement above.

another thing when asking that type of question, show some sample rows and the sample result you are looking for. that way people may know how to provide you a solution even when your approach may not be the correct way to solve the problem.

Ted Varnson
09-22-2006, 12:01 AM
Right I understand, I am only getting info from one column because I couldn't get one pulling from multiple columns to work.

That query was the one that has been working for me, however I can't get any other information sans the sum

What I'm hoping to achieve is the ability to figured money earned differently based on the 'employee_issalary' column.

For instance, if Hourly Employee works 10 hours at $10/h then it will figure 10*10 = $100. If Salaray Employee works 10 hours at $10/h it would then calculate 40*10 = $400 (where 40 hours is a full salary work week)