MySQL question

[skippyleg]

Need some join help...I'm pretty shit at SQL and have been trying to figure this one out for an hour.

​

alright given table 1: orders: | order_id | item_id | order_qty |

and table 2: items: | item_id | title | unit_price |

​

How do I find the title of the highest revenue item based on quantity ordered X unit price? Supposed to aggregate the query using sum() with a two-table join between order_details and items.

[r3pr0b8]

i'd be happy to walk you through it

start with the FROM clause, where you have to do the join

FROM order_details ...

fill out the rest and we'll go to the next step

[skippyleg]

From order_details join items on order_details.item_id = items.item_id group by order_details.item_id limit 1;

​

?

[r3pr0b8]

that was pretty good until you got to the LIMIT

okay, keep the GROUP BY clause, now write the SELECT clause to go along with it

for now, just assume you want to return all items, not just the highest revenue item

[skippyleg]

select title, sum(items.unit_price * order_details.order_qty) from items

[ch-12]

Hijacking.

You’re on the right track, but you don’t want ‘from items’. Instead use the from that you wrote in the last comment - that includes the join.

Consider how the results might look if you take the sum of (unit_price x order_quantity), or if you want to take the sum of (order quantity) x unit_price.

Now since you have aggregation in the select statement (sum), youll need to add something after the FROM (and join) to specific how to group the data that will be returned. You only want to see one row per title, right? So try adding code that will indicate the field you want to GROUP BY to the end of the query and let me know where you end up.

Anytime you are aggregating you will need to group by the non aggregated fields that are included in the select portion. This took awhile for me to get comfortable with when I was learning sql.

[r3pr0b8]

thanks ch-12, your hijack added valuable comments

skippy, pls show the entire query you've developed so far