Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    61

    Unanswered: help with sql statement

    can want to combine these two queries into one: (both of those work seperatly without errors)

    first query:
    SELECT Sum(MAIN.DAY+MAIN.NIGHT) AS TOTALTIME
    FROM MODEL LEFT JOIN MAIN ON MODEL.MODEL_ID =MAIN.MODEL_ID
    WHERE MODEL.MULTI='CHECKED' AND MAIN.PER_ID='$username'

    second query:

    SELECT MAIN.* FROM MODEL LEFT JOIN MAIN ON MODEL.MODEL_ID =MAIN.MODEL_ID
    WHERE MODEL.MULTI='CHECKED' AND MAIN.PER_ID='$username'

    I can't seem to combine them.
    I tried:
    SELECT Sum(MAIN.DAY+MAIN.NIGHT) AS TOTALTIME, MAIN.*
    FROM MODEL LEFT JOIN MAIN ON MODEL.MODEL_ID =MAIN.MODEL_ID
    WHERE MODEL.MULTI='CHECKED' AND MAIN.PER_ID='$username'

    I even tried specific files form the MAIN table just in case it was the * that was giving me problems:
    SELECT Sum(MAIN.DAY+MAIN.NIGHT) AS TOTALTIME, MAIN.DEST
    FROM MODEL LEFT JOIN MAIN ON MODEL.MODEL_ID =MAIN.MODEL_ID
    WHERE MODEL.MULTI='CHECKED' AND MAIN.PER_ID='$username'

    I also tried adding a thing called grouping but had no luck....
    Can anyone combine the 2 queries for me?
    Should i run them seperatly?
    How hard is it on a computer to run queries? CPU?
    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you probably do not need grouping

    SUM() is used to get the total of a number of column values, and not the addition of values on the same row

    SUM() requires grouping, and the groups of rows are what the SUM() operates on

    without grouping, your first query is merely:
    Code:
    select main.day+main.night as totaltime 
      from model 
    left outer
      join main 
        on model.model_id =main.model_id 
     where model.multi='checked' 
       and main.per_id='$username'
    see how main.day+main.night are added together? they produce a total for those two values on each row

    rudy

  3. #3
    Join Date
    Dec 2003
    Posts
    61
    thanks

    I was actually trying to add the values of the COLUMS

    But you tought me how i can add individual values.

    I can put them in a PHP while loop and get the SUM of the columns right?

    $TOTALTIME=0;
    $count=0;
    $result = mysql_query("SELECT DAY+NIGHT AS TT FROM MAIN WHERE PER_ID='$username'",$db);
    While ($myrow = mysql_fetch_array($result)){
    extract ($myrow);
    $TOTALTIME=($TT+$TOTALTIME);
    $count=$count+1;
    }

    echo "Total Time: $TOTALTIME hours.<br><br><br>";

    This gives me what i need. The total of the columns DAY and NIGHT.
    It also gives me the number of entries which are stores as $count.

    I'm getting the feeling like i'm taking the long way.
    Is there a simpler way to do all this?
    Thanks
    Noam

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, there is a simpler way

    you want one result, the sum for that particular user, so the entire result set is a group, the group of rows for that user, therefore you don't need a GROUP BY:
    Code:
    select sum(main.day+main.night) as totaltime 
      from model 
    left outer
      join main 
        on model.model_id =main.model_id 
     where model.multi='checked' 
       and main.per_id='$username'
    you cannot combine this with your other query, because this produces a total row, while the other query returns the detail rows

    if you want both detail rows and totals, then you would run simply the second, and do your totalling in a loop, as you suggested

    rudy

Posting Permissions

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