Multiple totals out of sql

Question

I have the code below

 $sql = "SELECT 
               date1, 
               date2, 
               userid, 
               jobid, 
               result, 
               price, 
               total, 
              (SELECT distinct SUM(total1) FROM Jobs) as total2
        FROM 
               Jobs 
          WHERE 
              total <= total1 
          GROUP BY 
              '$newphrase', '$newphrase1', '$user', '$job', result 
          ORDER BY 
              jobid DESC, userid DESC";
    $result = mysql_query($sql);

it outputs a grand total but it outputs it 3 times the output is below

SN01 0.17 15 2.55 25.05
SN01 0.50 15 7.5  25.05
SN01 1    15 15   25.05

the 25.05 is output 3 times i need it just once. I have tried distinct and everything i can think of Please help its driving me nuts

Answer 1

That's just how SQL works - I would recommend reading up on it.

Your main SELECT is returning three records and for each of those records it runs the inline SELECT SUM() for total2 - that's why you get the three totals.

The only way to stop that is to make the main SELECT return one record which there are various techniques.

If you still need the multiple records you will have to ignore the mutiple totals and only use the first one returned.

Answer 2

Throw everything but the totals into a temp table with all columns represented (including total). You should end up with a loaded table, but with null values in the total column. Then do a final query inserting the total into the temp table. Finally, select from the temp table.

Answer 3

SELECT DISTINCT refers to the ROW. Since you have different ROWs, you'll get them all back.

If all you want is the total, then why are you returning the other columns? NOTE: You might want to go back and edit your question: there are 8 columns in the SELECT but only 5 in the sample output (unless the dots are somehow seperating the column output)?

If it's truly killing you, you could rewrite the query to return similar rows like this:

SELECT DISTINCT
    date1, // Maps to column 1: SN01, 
    userid, // Maps to column 3: 15, 
    (SELECT distinct SUM(total1) 
     FROM Jobs) as total2 // Maps to Column 5: 25.05
FROM 
    Jobs 
WHERE 
    total <= total1 
GROUP BY 
    '$newphrase', '$newphrase1', '$user', '$job', result 
ORDER BY 
    jobid DESC, 
    userid DESC

Answer 4

If you need formatted output like that, you should probably use a reporting tool.

If you want a hack, use the sql case statement to get the value only for the first row, like this:

SELECT 
           date1, 
           date2, 
           userid, 
           jobid, 
           result, 
           price, 
           total, 
           case when rownum = 1 then
              (SELECT distinct SUM(total1) FROM Jobs)
           else
               null
           end as total2
    FROM 
           Jobs 
      WHERE 
          total <= total1 
      GROUP BY 
          '$newphrase', '$newphrase1', '$user', '$job', result 
      ORDER BY 
          jobid DESC, userid DESC

Warning: This is untested, database dependent code.