Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Apr 2004
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select column AND sum()

    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.

    PHP Code:
    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'] .  

  • #2
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    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.

  • #3
    New Coder
    Join Date
    Apr 2004
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, thanks a lot man!

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #5
    New Coder
    Join Date
    Apr 2004
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Last edited by Ted Varnson; 09-21-2006 at 11:04 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •