If you want to create charts, dashboards & reports from MySQL database, you can try Ubiq. Here’s an example of a table with above data, created using Ubiq. You can also use a reporting tool to plot this data on a table or dashboard and share it with your team. You can customize the above query to calculate percentage of two columns, as per your requirement. | sales_rep | sale | sale2 | sale3 | sale_pct | sale2_pct | sale3_pct | mysql> select sales_rep,sale, sale2,sale3, Demo Database Below is a selection from the 'Products' table in the Northwind sample database: COUNT () Example The following SQL statement finds the number of products: Example SELECT COUNT(ProductID) FROM Products Try it Yourself Note: NULL values are not counted. Then you can calculate percent of each column by totaling their values on the fly, as shown below. Mysql> insert into sales(sales_rep,sale,sale2,sale3) If your total data is spread across multiple columns, as shown below, mysql> create table sales(sales_rep varchar(255),sale int, sale2 int,sale3 int) ,round((sale/goal)*100,2) as attainment_percentīonus Read : How to Calculate Moving Average in MySQL If you want to calculate percentage of two columns only for specific rows, add WHERE clause as shown below in bold mysql> select sales_rep,sale,goal Round((sale/goal)*100,2) as attainment_percent from sales If you want to round the percent numbers to 2 decimal places, you can use the ROUND function as shown mysql> select sales_rep,sale,goal, | sales_rep | sale | goal | attainment_percent |īonus Read : How to Automate Pivot Table Queries in MySQL (sale/goal)*100 as attainment_percent from sales , successful, 10, 50, failed, 5, 25, refunded, 5, 25, successful, 14, 58.3, failed, 4, 16. You can directly calculate it on the fly using division and multiplication operators, as shown below. Here’s the SQL query to calculate percentage of two columns (sale, goal). Let’s say you want to calculate attainment_percent as sale/goal*100 for each sales rep. Mysql> insert into sales(sales_rep,sale,goal) Mysql> create table sales(sales_rep varchar(255),sale int, goal int) Let’s say you have the following table – sales(sales_rep,sale,goal) Here are the SQL to calculate percentage of two columns in MySQL. How to Calculate Percentage of Two Columns in MySQL Here’s how to easily calculate percentage of two columns in MySQL. HAVING 100*count(p.id)/(SELECT count(p.Sometimes you may need to simply calculate a percentage of two columns in MySQL databases. HAVING 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe On p.id = pe.participant_id) as percentage resolution'Cancelled' is an expression returning 0 for false, or 1 for true. , 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe 1 Answer Sorted by: 1 To get the percentage you need to divide the sum of the value, with the total and multiply by 100 to get a percentage. So i.signori answer will be : SELECT count(p.id) as count TO SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe on p.id = pe.participant_id),1) as percentage, p.Nationality So modify your initial query from : SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage, p.Nationality Please check in your initial query to use the same sets: HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) <= 1 HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) > 1įor the second part, try to combine the results of the first with those of the sum of percentages less than 1. , round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage Try using Having clause: SELECT count(p.id) as count So with the new query the results will be: +-+-+-+ I want to modify the query in order to don't show any rows with percentage less than 1% but summarize their number and add is as 'Other' with the new percentage. JOIN participants_events as pe on p.id = pe.participant_id This is the query I am using to get the percentage per country for participants in events: SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage, p.Nationality
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |