June 26, 2019 at 2:54 pm
This is for SQL Server 8.0 and I'm using MySQL Workbench on this.
I have a table called 'business_ledger' and the following fields: employee_name (various string values to denote employees - not used in this query but it's part of the database), title (with values: Novice, Journeyman, Master), Earnings (various int values), certification (with values: None, In Progress, Certified), and percent (this will be created later).
The first query is to sort the total earnings per title. I'm putting an example of what it looks like currently with simplified earnings so the 2nd portion makes more sense. The total earnings do seem to be correct based on the database values.
(Table 1)
Title | Earnings
Novice | 100
Journeyman | 100
Master | 100
Now I need to also separate the title out by certification. In addition to that, I need a percentage of each certification within each title group. I'm not sure how to do the latter.
The expected output should be something like this (I had each 'title's earnings add up to $100 just for the purpose of the example). The 'percent' field should total to 100% for each title too, not for all earnings, etc.:
(Table 2)
Title | Certification | Earnings | Percent
Novice | None | 25 | 0.25
Novice | In Progress | 60 | 0.60
Novice | Certified | 15 | 0.15
Journeyman | None | 20 | 0.20
Journeyman | In Progress | 40 | 0.40
Journeyman | Certified | 40 | 0.40
Master | None | 35 | 0.35
Master | In Progress | 15 | 0.15
Master | Certified | 50 | 0.50
The second query is as far as I've gotten and does seem to separate out each title by certification (sort of like above). However it doesn't seem to be working quite right because in some cases the Earnings column has '0'. I know in the database, certain 'employee_name's have '0' for their earnings. MySQL seems to be not totalling all of these, and instead it seems like if there's a 0 in the earnings field of a certification, it makes the earnings for that whole certification 0 without adding the other non-zero values. I'm not sure I'm explaining this well so an example is below (I'm omitting the percent since I don't actually have that yet).
(Table 3)
Title | Certification | Earnings |
Novice | None | 0 |
Novice | In Progress | 60 |
Novice | Certified | 15 |
Journeyman | None | 20 |
Journeyman | In Progress | 0 |
Journeyman | Certified | 40 |
Master | None | 35 |
Master | In Progress | 15 |
Master | Certified | 0 |
But I know there are Novice, None-Certification employees who did earn something, but those aren't being taken into account for some reason. So for example, I know there's 100 worth of earnings for all of 'Novice', but my query's only showing 75 for some reason.
I also need to total the earnings of each title and determine the percentage of each title that has a specific certification and mark that as its own column 'percent'. I know the percent will need to be something like: "(earnings_of_certification) / (total_earnings_of_title) as percent", but I'm not sure how to get the total_earnings_of_title without getting the total earnings of -all- titles. I'm guessing it's something like:
sum(earnings) when title = 'Novice' as Novice_Earnings;
sum(earnings) when title = 'Journeyman' as Journeyman_Earnings;
sum(earnings) when title = 'Master' as Master_Earnings;
But I'm not sure how to translate that pseudo-code into MySQL, or how to pull data just from the 'Novice, In Progress' Earning's field / Novice_Earnings to get the percentage.
First code
SELECT title, sum(earnings) as 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'd get something like Table 2, without SQL ignoring certain cases where the value is 0 (like in Table 3). I'm basically trying to make sure I'm totaling certain rows in a column (all the earnings of a Novice employee with None as their certification for instance), then divide certain 'cells' within a column (Such as Novice->None's earnings and row by that total for all of the Novice earnings.
June 26, 2019 at 5:07 pm
This site is devoted to Transact-SQL (MS SQL Server), so you may not get an answer to a MySQL question.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 2, 2019 at 6:12 pm
It looks like you're missing the actual SUM in the second query. I would expect it to be something along these lines:
SELECT
title, certification, SUM(earnings) as earnings
FROM business_ledger
WHERE certification in
(SELECT certification
FROM business_ledger
)
GROUP BY title, certification
ORDER BY title, certification
In MS SQL Server, the way you had it would generate an error since you had a column in the select that wasn't doing a calculation but wasn't included in the group by. I'm not sure what MySQL's behavior is with that.
You're where clause also seems like an unnecessary criteria unless you're really looking at a different table in your database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply