1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to SUM only certain rows of a column, save to variables, other misc.

Discussion in 'MySQL' started by Kai123, Jun 26, 2019.

  1. #1
    I'm on MySQL 8.0 and using MySQL Workbench. I just started to learn MySQL and I'm having a lot of trouble figuring out how to do 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.
     
    Kai123, Jun 26, 2019 IP