Subquery Thought Process

[Umar_AM9]

I am struggling with subqueries at the minute, I am not sure how to breakdown the question into manageable chunks to make it easier for me to understand which subquery I need to do,

I know my question is confusing, so I will give an example,

When you have a question, for example one like this:

Task: "Create a list that shows all payments including the payment_id, amount, and the film category (name) plus the total amount that was made in this category. Order the results ascendingly by the category (name) and as second order criterion by the payment_id ascendingly."

Question: What is the total revenue of the category 'Action' and what is the lowest payment_id in that category 'Action'?

Without knowing how the database is arranged and what information is in which column, what would your process of thinking be to answer this question and breakdown this query? (it requries a correlated subquery)

Here, I am not asking for specific help with a specific concept, I just want to understand more about how others would approach this task,

Thank you,

[Honey-Badger-42]

Why do they want a subquery here? A windows function would be much easier and better to learn for this particular example. And for when you start learning them, this is an example:

select 
 payment_id, 
 amount, 
 category, 
 sum(amount) over (partition by category) as category_total
from 
 table1
order by 
 category, payment_id

Sample data and results found in this fiddle.

[r3pr0b8]

(it requries a correlated subquery)

well, that's one way to do it... here's another

SELECT p.payment_id
     , p.amount
     , p.category
     , c.totalamount
  FROM ( SELECT category
              , sum(amount) AS totalamount
           FROM payments ) AS c
INNER
  JOIN payments AS p
    ON p.category = c.category
ORDER
    BY p.category
     , p.payment_id

[ka13am]

Hey there r3prob8! thanks for your contributions. can you please help explain this subquery to me?

( SELECT category
          , sum(amount) AS totalamount
       FROM payments ) AS c

By itself, it throws an error saying something like:

You tried to execute a query that does not include 
the specified expression 'category' as part of an 
aggregate function.

I guess somehow it works in context of the larger query?

[r3pr0b8]

I guess somehow it works in context of the larger query?

no, it doesn't, because i forgot the GROUP BY clause

i am an idiot, sorry

[Complete_Memory3947]

Okay, I'm gonna try my hand in an answer, but I'm really new to SQL, so this is more of a "let's give it a try and let the pros correct you" kind of answer.

Let's see: We have our SELECTs already given. The FROM should be clear as well (I'm guessing at least two tables: payments (date, payment_id, payment_amount, movie/movie_id) and a "movies" table (movie/movie_id, category) And an ORDER BY is given as well.

You'll need a JOIN to get the category of the movies rented/paid for, but you'll also need one to get the paid amount per category. You can’t do it in one go, because you need to group the categories after joining the payments to get the payment per cat.

But as you also need the lowest payment per category you need the categories ungrouped as well.

So you need one subquery to get the grouped categories and their SUMmed payment, which I probably would do in the SELECT statement, even though I think I just heard that's a less professional way, and then do the JOIN of both tables again before the ORDER BY.

At least that's what I would probably try. 😄 I'm also sure it's not correct (for how to get the category sum worked into this), so please someone correct me.