[RESOLVED] Most efficient way of getting a totals column for data from SQL using PHP & MySQL

This site is best viewed in a modern browser with JavaScript enabled.

[RESOLVED] Most efficient way of getting a totals column for data from SQL using PHP & MySQL

php-bgrader

I have a PHP script which queries the database and returns a number of matching rows as follows:

SELECT name, open_wip, clock_hrs, close_wip, avail_hrs, sold_hrs, sold_amt, cost_hrs, cost_amt, labour_gross, margin, docs, unprod_hrs, sold_avail FROM ReportData

What I want is a final row which is a total of each column.

I'm thinking there are 2 options:

Run another SQL to get the sum of each row and add it to the array I create
Use PHP to add all the rows together (don't know how to do this yet inside the array - was just assuminf I'd loop through each row adding it to the last)

I do not see either method as efficient and was wondering if there is a function I don't know of that can make it easier simpler?

NogDog

You could use GROUP BY ... WITH ROLLUP

SELECT 
    name, SUM(open_wip), SUM(clock_hrs), SUM(close_wip), 
    SUM(avail_hrs), SUM(sold_hrs), SUM(sold_amt), 
    SUM(cost_hrs), SUM(cost_amt), SUM(labour_gross), 
    SUM(margin), SUM(docs), SUM(unprod_hrs), SUM(sold_avail) 
FROM ReportData
GROUP BY name WITH ROLLUP

php-bgrader

Thanks for that.

When grouping with 2 columns though, it seems to duplicate the resultset (I'm using both userid & name in group by)

My slight modification:

SELECT 
    userid, name, SUM(open_wip), SUM(clock_hrs), SUM(close_wip), 
    SUM(avail_hrs), SUM(sold_hrs), SUM(sold_amt), 
    SUM(cost_hrs), SUM(cost_amt), SUM(labour_gross), 
    SUM(margin), SUM(docs), SUM(unprod_hrs), SUM(sold_avail) 
FROM ReportData
GROUP BY userid, name WITH ROLLUP

It appears to summarise each row by userid (which just duplicates the line exactly as is) and then totals all rows together at the bottom.

I've done some searching and this appears to be expected behaviour?

Is there no way to suppress a sub-total on id and just provide a total on all rows?

NogDog

If you can't get it to work that way (maybe throwing in some logic in the application code to ignore the sub-totals ?), I'd probably just go with separate queries. If the table is properly indexed, it's probably at least as fast as running some sort of loop/counter logic in your application code to get the totals. In fact, you could do it in one transaction by using UNION, though you have to make sure each column in the total query is the same type as its respective column in the main query, e.g.:

(
  SELECT 
    userid, name, open_wip, clock_hrs, close_wip, 
    avail_hrs, sold_hrs, sold_amt, 
    cost_hrs, cost_amt, labour_gross, 
    margin, docs, unprod_hrs, sold_avail 
  FROM ReportData
)
UNION
(
  SELECT 
    0, 'totals', SUM(open_wip), SUM(clock_hrs), SUM(close_wip), 
    SUM(avail_hrs), SUM(sold_hrs), SUM(sold_amt), 
    SUM(cost_hrs), SUM(cost_amt), SUM(labour_gross), 
    SUM(margin), SUM(docs), SUM(unprod_hrs), SUM(sold_avail) 
  FROM ReportData
)

php-bgrader

Thanks,

ended up going with union and it works as required.