www.webdeveloper.com
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Dec 2013
    Location
    Melbourne, Australia
    Posts
    26

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

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

    Code:
    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:

    1. Run another SQL to get the sum of each row and add it to the array I create
    2. 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?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,623
    You could use GROUP BY ... WITH ROLLUP
    Code:
    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
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Dec 2013
    Location
    Melbourne, Australia
    Posts
    26
    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:

    Code:
    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?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,623
    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.:
    Code:
    (
      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
    )
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Dec 2013
    Location
    Melbourne, Australia
    Posts
    26
    Thanks,

    ended up going with union and it works as required.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles