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.
Calculate td row percentages and overall column percentages
Moderator: General Moderators
Re: Calculate td row percentages and overall column percenta
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.
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
Example:
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.
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 |
# +--------+-------+------------+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
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?
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
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.
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
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
I don't have links or examples. Fortunately, there are search engines.