New to SQL - difficulties in SUMing only certain rows of a column, other misc.

  • Kai

    SSC Enthusiast

    Points: 112

    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.

  • drew.allen

    SSC Guru

    Points: 76548

    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

  • LightVader

    Hall of Fame

    Points: 3618

    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.



    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply