Calculate td row percentages and overall column percentages

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Calculate td row percentages and overall column percentages

Post by cjkeane »

Hi everyone.

I'm wondering how to use php and mysql to calculate td row percentages. for eg.

if a table has data related to clients serviced with a case status of either open or closed, that data needs to be displayed from mysql using php in a table where each td has the percentage of the total calculated. based on that info, how would the data be displayed using php and a mysql query to display the data as follows:

case status percentage of total
open 20%
closed 80%
total 100%

the total would always be 100%, but of course, the open and closed percentage totals would always change.

i'm really not sure how to approach building the query, so any help would be appreciated to guide me in the right direction. thanks.
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Calculate td row percentages and overall column percenta

Post by oscardog »

Depending on the traffic to the website, it would probably be easier just to do two separate queries as the difference in speed is negligible and not really an issue for a low-traffic website.

So query one you select all cases that are open, query two you select all cases that are closed.

You then mysql_num_rows() both, add the numbers together and then work out your percentages as you would normally.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Calculate td row percentages and overall column percenta

Post by McInfo »

Example:

Code: Select all

DESCRIBE `cases`;
# +--------+-----------------------+------+-----+---------+----------------+
# | Field  | Type                  | Null | Key | Default | Extra          |
# +--------+-----------------------+------+-----+---------+----------------+
# | num    | int(3) unsigned       | NO   | PRI |         | auto_increment |
# | status | enum('open','closed') | NO   |     |         |                |
# +--------+-----------------------+------+-----+---------+----------------+

SELECT * FROM `cases`;
# +-----+--------+
# | num | status |
# +-----+--------+
# |   1 | open   |
# |   2 | open   |
# |   3 | closed |
# |   4 | open   |
# |   5 | closed |
# |   6 | open   |
# |   7 | closed |
# |   8 | open   |
# |   9 | closed |
# |  10 | closed |
# |  11 | open   |
# |  12 | closed |
# |  13 | closed |
# |  14 | closed |
# +-----+--------+

Code: Select all

SELECT
    `status`,
    COUNT(*) AS `count`,
    ROUND(100 * COUNT(*) / `num_rows`, 2) AS `percentage`
FROM
    `cases`,
    (SELECT COUNT(*) AS `num_rows` FROM `cases`) AS `c`
GROUP BY
    `status`
WITH ROLLUP;
# +--------+-------+------------+
# | status | count | percentage |
# +--------+-------+------------+
# | open   |     6 |      42.86 |
# | closed |     8 |      57.14 |
# |        |    14 |     100.00 |
# +--------+-------+------------+
The WITH ROLLUP clause adds the last row which contains column totals. The `status` total is NULL.

A subquery gets the total row count so that number can be used by each of the rows in the main query.

The percentage is optionally rounded to two decimal places.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: Calculate td row percentages and overall column percenta

Post by cjkeane »

thats great. thanks. i wasn't aware there was a totaling feature built into mysql. i was doing the query in two parts and the overall total was always off by a few cents. with your suggestion, it seems like the calculation works out more accurately.

i do have another question:

there are other details which need to be hidden / unhidden when either open or closed is clicked.
eg.

when open is clicked, the table would appear like:

OPEN
case# 101
case# 102
case# 103
CLOSED

when closed would be click, the details under open would hide and the details under closed would be visible eg

OPEN
CLOSED
case# 104
case# 105
case# 106

can this be done easily?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Calculate td row percentages and overall column percenta

Post by McInfo »

The animation could be done with JavaScript. Hiding a row might involve changing the display CSS property either directly through the row's style DOM attribute or indirectly by changing the row's className after relevant "visible"/"hidden" CSS classes have been defined.

The data is different from what the query in my last post would be capable of providing. You would need a different query, probably a basic SELECT with an ORDER BY clause. The partition point between two status groups would be determined while looping through the rows by comparing the status of the current row with the status of the previous row.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: Calculate td row percentages and overall column percenta

Post by cjkeane »

thanks. i knew it was something along those lines. do you have any links or examples of how to code that? thanks.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Calculate td row percentages and overall column percenta

Post by McInfo »

I don't have links or examples. Fortunately, there are search engines.
Post Reply