Page 1 of 1
Calculate td row percentages and overall column percentages
Posted: Sun May 01, 2011 1:31 pm
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.
Re: Calculate td row percentages and overall column percenta
Posted: Sun May 01, 2011 1:56 pm
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.
Re: Calculate td row percentages and overall column percenta
Posted: Sun May 01, 2011 5:43 pm
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.
Re: Calculate td row percentages and overall column percenta
Posted: Mon May 02, 2011 8:07 pm
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?
Re: Calculate td row percentages and overall column percenta
Posted: Tue May 03, 2011 12:22 am
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.
Re: Calculate td row percentages and overall column percenta
Posted: Tue May 03, 2011 4:20 pm
by cjkeane
thanks. i knew it was something along those lines. do you have any links or examples of how to code that? thanks.
Re: Calculate td row percentages and overall column percenta
Posted: Wed May 04, 2011 1:15 am
by McInfo
I don't have links or examples. Fortunately, there are search engines.