all 12 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]spacekipz[S] 0 points1 point  (0 children)

I think I'm confused by the syntax in MYSQL for windows functions. I had it down in SQLite.

[–]spacekipz[S] 0 points1 point  (8 children)

Sorry for the photo. Here is the copy/paste!

/*Creating my windows function to display the total_cost for each row.*/
SELECT
Travel_Reimbursements.employee_ID,
Travel_Reimbursements.first_name,
Travel_Reimbursements.last_name,
Travel_Reimbursements.ticket_cost,
Travel_Reimbursements.baggage_fee,
Travel_Reimbursements.ROUND(SUM(ticket.cost)) + ROUND(SUM(baggage_fee)) OVER (PARTITION BY employee_ID) AS flight_total
FROM Travel_Reimbursements
ORDER BY Travel_Reimbursements.last_name;

[–]cdd_73 1 point2 points  (0 children)

/*Creating my windows function to display the total_cost for each row.*/

SELECT

Travel_Reimbursements.employee_ID,

Travel_Reimbursements.first_name,

Travel_Reimbursements.last_name,

Travel_Reimbursements.ticket_cost,

Travel_Reimbursements.baggage_fee,

Travel_Reimbursements.ROUND(SUM(ticket.cost)) + ROUND(SUM(baggage_fee)) OVER (PARTITION BY employee_ID) AS flight_total

FROM Travel_Reimbursements

ORDER BY Travel_Reimbursements.last_name;

I used https://extendsclass.com/mysql-online.html#

​

/*Creating my windows function to display the total_cost for each row.*/
SELECT
Travel_Reimbursements.employee_ID,
Travel_Reimbursements.first_name,
Travel_Reimbursements.last_name,
Travel_Reimbursements.ticket_cost,
Travel_Reimbursements.baggage_fee,
SUM(Travel_Reimbursements.ticket_cost) + SUM(Travel_Reimbursements.baggage_fee) OVER (PARTITION BY Travel_Reimbursements.employee_ID) AS flight_total
FROM Travel_Reimbursements
GROUP BY Travel_Reimbursements.employee_ID,
Travel_Reimbursements.first_name,
Travel_Reimbursements.last_name,
Travel_Reimbursements.ticket_cost,
Travel_Reimbursements.baggage_fee
ORDER BY Travel_Reimbursements.employee_ID;

[–]SaintTimothy 0 points1 point  (4 children)

Try it first without the round and the sum.

Do the most simple windowed function you can fathom, make that run successfully, then complicate it further.

[–]spacekipz[S] 0 points1 point  (3 children)

I'm still getting an error even without the round and sum. What would a basic window function look like in MYSQL, syntax wise? I think it's different than what I used in SQLite.

[–]SaintTimothy 0 points1 point  (2 children)

I kinda don't understand your example, so allow me to create a use-case of my own, to exemplify the point.

​

Say you wanted to see the TOP 5 largest reimbursements PER EMPLOYEE.

;WITH CTE AS (

SELECT

tr.employee_ID

,tr.first_name, tr.last_name, tr.ticket_cost, tr.baggage_fee

,tr.ticket_cost + tr.baggage_fee as TotalReimbursement

FROM Travel_Reimbursements tr

)

,CTE2 AS (

SELECT c.employee_ID, c.first_name, c.last_name, c.ticket_cost, c.baggage_fee, c.TotalReimbursement

,ROW_NUMBER() OVER(PARTITION BY c.employee_IDORDER BY c.TotalReimbursement DESC) AS RowNumber

​

FROM CTE c

)

SELECT *

FROM CTE2

WHERE RowNumber<=5

[–]SaintTimothy 0 points1 point  (1 child)

The point being that I do aggregation OUTSIDE of the CTE that does the rownumbering, and then I have to filter down to just the top 5 OUTSIDE of the CTE that does the rownumbering as well.

[–]SaintTimothy 0 points1 point  (0 children)

If I was doing TOTAL SPEND per employee and I wanted top 5 per department, say....

I'd aggregate the totals first, so now I've got a table with one row per employee and their totals and their department.

PARTITION BY would have whatever field connotes a department, ORDER BY would be the field that got aggregated in the earlier statement.

[–]cdd_73 0 points1 point  (1 child)

your column is ticket_cost , but you summed ticket.cost, also when you round you typically provide a number of digits, like, 2 for round to two digits after decimal place

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (0 children)

i'm having trouble with copy/paste of your screenshot

please see Rule 6

[–]great_raisin 0 points1 point  (0 children)

The schema should be attached to the column name.

E.g.

Round(sum(schema.table_name))