Hi,

I'm a massive beginner of SQL and just having trouble finishing this problem. I need to get the Max of SUM(price) and output both this and e.name. I'm just not to sure how to go about it, im guessing I need to use a subquerry.

SELECT e.name, SUM(price)
FROM hsTreatment t, hsVisitHistory V, hsEmployee e
WHERE t.treatmentid = v.treatmentid
AND e.empid = v.stylistid
GROUP BY e.name;

SELECT e.name, max.total
FROM
        (
        SELECT empid,total FROM
        (
         SELECT e.empid AS "empid", sum(price) AS "total"
         FROM hstreatment t,hsvisithistory v,hsemployee e
         WHERE t.treatmentid=v.treatmentid AND e.empid=v.stylistid
         GROUP BY e.empid
        ) tt
        WHERE
         total=(
                SELECT max(total) FROM
                (
                SELECT sum(price) AS "total"
                FROM hstreatment t,hsvisithistory v,hsemployee e
                WHERE t.treatmentid=v.treatmentid AND e.empid=v.stylistid
                GROUP BY e.empid
                ) tt
                )
        ) max,
        hsemployee e
WHERE max.empid=e.empid
As I understand the problem is to find the employee with highest price paid for treatment.
You can give a try to the above, but bear in mind it was not tested on Oracle so you may have to tweak the syntax a bit.
Note that I changed the grouping criteria to employee id (instead of employee name) as this seems natural to me. Beacause of this change we need the outermost select to retrieve the employee name based on the desired id.

Please, do not fire the most expensive employee!

Please, let me know whether the above worked or not.

U need antivirus?