[SOLVED] SUM() help

[xtopolis]

I need SUM() help.. get it.. lol.

 

I want to get the total SUM of the table for the columns price and gratuity as a Total.

I seem to either get an invalid statement, double the price field if gratuity is empty, or an aggregate sum of the previous sum as it goes through the rows.

 

SELECT 
  DATE_FORMAT(etr.etime, '%b %e (%a)') AS 'Date',
  DATE_FORMAT(etr.etime, '%l:%i %p') as 'Time of Day',
  DATEDIFF(CURDATE(), etr.etime) AS 'Days Prior',
  GROUP_CONCAT(eta.name ORDER BY eta.tid ASC  SEPARATOR ', ') as 'Tags',
  (etr.price + etr.gratuity) as 'Cost',
  SUM(etr.price + etr.gratuity) AS 'Total'

  FROM `expense_tracker` etr
  JOIN `expense_tags_tracker` ett ON(etr.eid=ett.eid)
  JOIN `expense_tags` eta ON(eta.tid=ett.tid) 
  GROUP BY etime
  ORDER BY etime DESC
  LIMIT 0,13

price, gratuity are on the same table: expense_tracker

only 1 row has a gratuity value, the gas row.

 

Statement returns:[pre]

Date          Time of Day      Days Prior    Tags              Cost      Total

Oct 24 (Fri) 6:45 PM          1              food, rubios      8.17      16.34

Oct 23 (Thu) 6:56 PM          2              food, mcdonalds    3.23      6.46

Oct 22 (Wed) 6:42 PM          3              food, subway      6.61      13.22

Oct 22 (Wed) 10:20 AM          3              gas                37.07    37.07

Oct 21 (Tue) 6:53 PM          4              food, rubios      9.68      19.36

Oct 21 (Tue) 9:16 AM          4              starbucks          3.85      3.85

Oct 20 (Mon) 6:02 PM          5              food, deltaco      2.13      4.26

[/pre]

Structure

-- 
-- Table structure for table `expense_tracker`
-- 

CREATE TABLE `expense_tracker` (
  `eid` int(11) NOT NULL auto_increment,
  `price` decimal(6,2) NOT NULL,
  `gratuity` decimal(6,2) NOT NULL,
  `etime` datetime NOT NULL,
  PRIMARY KEY  (`eid`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=20 ;

[JasonLewis]

Try making it like this:

 

(SUM(etr.price) + SUM(etr.gratuity)) AS 'Total'

[xtopolis]

That returns the same result for total. =/  Thanks for trying

With the current data I need it to return the total which is around $70 or so last I checked.

[Barand]

As you are grouping by etime, the price and gratuity would need the same times to be accumulated together. Without sight of the data, diagnosis ability is somewhat limited.

[xtopolis]

price and gratuity are on the same table.

 

If I do a "SELECT SUM(price+gratuity) FROM `expense_tracker` WHERE 1" it produces the correct result.

 

Currently I just use PHP to tally the total while it loops through the results, buffering the output into a variable ($output .= ...) then outputting the php tally result (so it can be at the top) then output the $output.

 

I think this is the best way, other than doing a separate query...