SQL Query – select multiple rows from 2 tables

I’m new to SQL and recently saw this question, which is described on the [attached picture]

. Any suggestions on how to solve it? Thanks in advance.

Answer

In most cases such requirements are better to resolve by starting from the end. Let’s try that: “have more than 3 items … and total price”.

select InvoiceId, sum(price)
  from InvoiceItem
 group by InvoiceId
having count(1) > 3;

This query gives you the overall price for every invoice that has more than 3 items. You might be wondering why there is that funny “having” clause and not the “where” clause.

The database executes first “from” and “where” parts of the query to get the data and only after that using aggregations (sum, count etc.) is possible, so they are to be specified afterwards.

The query above actually returns all the data from requirement, but I assume that whoever gave you this task (that was a teacher, right?) was asking you to retrieve all the data from Invoices table that correspond to the records within the InvoiceItem table that has more than 3 items and so on.

So, for now all you have left is to join the Invoice table with a query from above

select i.id, i.customername, i.issuedate, sum(it.price)
  from InvoiceItem it
  join Invoice i on it.invoiceid = i.id
 group by i.id, i.customername, i.issuedate
having count(1) > 3;

Teacher might ask you why did you use count(1) and not count(*) or count(id) or something. Just tell him/her all these things are equal so you picked just one.

Now it should be working fine presumably. I did not tested it at all as you did not provide the data to test it on and it is late so I am pretty tired. Because of this you might needed to fix some typos or syntax errors I probably made – but hey let’s keep in mind you still have to do something on your own with this task.