mysql> select sales_rep,sale, sale2,sale3, 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 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. Within the code first we will connect to MySQL database.Sometimes you may need to simply calculate a percentage of two columns in MySQL databases. We can execute the above code and display records by using PHP. Storing sum of each record in different table PHP code to display This will be useful for final report printing or displaying. We can store sum of marks in each subjects of each student in a different table. WHEN sum(social + math + science ) >=170 THEN 'C' WHEN sum(social + math + science ) >=200 THEN 'B' Read more on CASE WHEN THEN statement here.ĬASE WHEN sum(social + math + science ) >= 225 THEN 'A' Here we will check total mark against set marks for different levels of grade and allot Grade accordingly. MySQL Case query executes the statement based on the matching condition. We can display grade of the student by using CASE statement in our Query. Show Grade based on the total mark ( using CASE statement ) Sum(social)+sum(math)+sum(science) from student_sum Note that we have to maintain same number of columns while using UNION query. Like we do in MS Excel we can display sum of all columns and the sum of all total subject marks by using UNION. LEAST(social,science,math) as min FROM student_sum idĭisplaying sum of all columns at last row We can list starting from highest mark to lowest mark by using Order By Query.įrom student_sum GROUP BY id ORDER BY Total DESC Displaying highest & lowest mark in all subjects of each student SELECT id,name,GREATEST(social,science,math) as max, Listing all columns along with aggregate functions like sum(), max() using over() with partition → Displaying from Highest to lowest SELECT id,name,class, social, math, science, Note that id is unique value column in our table. Now we will try to display all marks and sum of them for each student by using group by. Query to display percentage value using 2 decimal places.įORMAT((( social + science + math)/(95*3) * 100),2) AS percentageįROM `student_sum` Sum of the row data of each record The above percentage data is having four decimal places, we can format these numbers by using ROUND Sql command. (( social + science + math)/(95*3) * 100) AS percentage SELECT id, name, class,( social + science + math) as Total, SELECT name, (( social + science + math)/(95*3) * 100) AS percentageįROM `student_sum` Displaying Total Mark with Percentageīoth total mark and percentage we can display like this So to get the percentage mark of each student we have to multiply 95 with 3 ( for three subjects ) and then divide that from the total and multiply with 100 here is the query. Let us assume the full mark in each subject is 95. We can add various other sql commands like where clause etc. SELECT sum(social + math + science ) as total So the sum is to be collected in all three columns. Now we will try to get sum of marks of all students in all subjects. Related Tutorial MySQL SUM MySQL Max MySQL Min MySQL AvgĪs you can see the above sum is sum of marks of each student.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |