I am on MySQL 8.0 and use MySQL Workbench. I have just started to learn MySQL and I have a lot of trouble understanding how to do that.
I have a table called 'business_ledger' and the following fields: employee_name (different string values to designate employees – not used in this query, but this is part of the database), title (with the values: Novice, Journeyman, Master), Earnings (various int ), certification (with the values: None, In Progress, Certified) and% (this option will be created later).
The first query is to sort the total earnings by title. I give an example of what it currently looks like with simplified earnings, so the second part makes more sense. The total gains appear to be correct based on the values of the database.
Title | Earnings
Novice | 100
Companion | 100
Master | Now, I also have to separate the title by certification. In addition to that, I need a percentage of each certification in each group of titles. I do not know how to do it.
The expected release should be something like this (I've had the sum of the earnings of each title up to $ 100 just for the purposes of the example). The field "Percentage" must also total 100% for each title, not for all income, etc .:
Title | Certification | The gains | Percent
Novice | None | 25 | 0.25
Novice | In progress | 60 | 0.60
Novice | Certified | 15 | 0.15
Companion | None | 20 | 0.20
Companion | In progress | 40 | 0.40
Companion | Certified | 40 | 0.40
Master | None | 35 | 0.35
Master | In progress | 15 | 0.15
Master | Certified | 50 | 0.50 The second query concerns everything I've got and seems to separate each title by certification (a bit like above). However, this does not seem to work properly because in some cases the earnings column contains "0". I know that in the database, some 'employee_name have & # 39; 0 for their income. MySQL does not seem to total all of this, and it seems rather that if there is a 0 in the revenue field of a certification, it generates a profit for this 0 certification without adding the other non-zero values. I am not sure to explain this well, so an example is presented below (I do not know the percentage since I do not have it yet).
Title | Certification | The gains |
Novice | None | 0 |
Novice | In progress | 60 |
Novice | Certified | 15 |
Companion | None | 20 |
Companion | In progress | 0 |
Companion | Certified | 40 |
Master | None | 35 |
Master | In progress | 15 |
Master | Certified | 0 | But I know that there are novice employees, without certification, who have earned something, but these are not taken into account for any reason. So, for example, I know that there is 100% win for "Novice", but my query only displays 75 for any reason.
I must also tabulate the winnings of each title and determine the percentage of each title that has a specific certification and mark it as its own "percentage" column. I know the percentage will have to be something like: "(credits_of_certification) / (total_earnings_of_title) in percentage", but I'm not sure how to get the total_earnings_of_title without getting the total income from -all- titles. I guess it's something like:
sum (earnings) when title = "Novice" as Novice_Earnings; sum (earnings) when title = 'Companion & # 39; as Companion_Earnings; sum (earnings) when title = & # 39; Master & # 39; as Master_Earnings; But I'm not sure how to translate this pseudo-code into MySQL, or how to extract data simply from the Winner's "Novice, Current" field. / Novice_Earnings to get the percentage.
First SELECT code title, sum (earnings) in earnings FROM business_ledger GROUP BY title 2nd code SELECT title, certification, earnings FROM business_ledger WHERE certification in (SELECT certification FROM business_ledger) GROUP BY title, certification ORDER BY title, certification Ideally, I would like to get something like Table 2, without SQL ignoring some cases where the value is 0 (as in Table 3). I'm essentially trying to make sure to total some rows of a column (all incomes of a novice employee whose certification is none, for example) and then divides some of them. " cells "in a column (like Novice-> No wins and line by this total for all novice wins.