0

Im fiddling around with w3schools northwind DB. https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_count

What im trying to accomplish: A result like this:

orderid       suppliername                   numberOfProducts
10300         New Orleans Cajun Delights                    2

The problem here: The respective order actually requires two DIFFERENT suppliers. This isnt reflected in the result to my query.

Here is the query:

SELECT  o.orderid, s.suppliername, COUNT(p.productname) AS numberOfProducts
FROM Suppliers s
JOIN Orders o
ON o.shipperid = s.supplierid
JOIN OrderDetails od
ON od.orderid = o.orderid
JOIN Products p 
ON p.productid = od.productid
GROUP BY o.orderid, s.suppliername
HAVING o.orderid = 10300;

I'm rather confused. As far as I could find on SO, grouping by multiple columns is possible in mysql: GROUP BY behavior when no aggregate functions are present in the SELECT clause

The "priorization" goes from left to right in the GROUP BY clause. So out of the above query, I would expect the following behavior:

First, group as many identical resultsets as possible for the column O.OrderID into one row. Second, group as many identical results as possible for the column S.SupplierName into one row. For each resulting row, aggregate the number of resultsets for the column P.ProductName.

But currently, it does only half the job. It basically forgets to group the individual resultsets for the column S.SupplierName accordingly. So here I only see one Supplier supplying us with 2 products for this order, while in reality, it is two suppliers, each of them supplying us with one single product for the order.

What am I missing?

Narktor
  • 977
  • 14
  • 34
  • It's odd to use HAVING this way, but yep. Nothing wrong here. – Strawberry Jun 05 '20 at 10:20
  • @Strawberry Well, you can omit HAVING and get an undesired result as well. Only 3 orders then, with randomly aggregated results on productname. – Narktor Jun 05 '20 at 10:29
  • The results may be "undesired" but that just means you are not writing code that does what you want & the code doesn't do what you think it does. – philipxy Jun 05 '20 at 10:30
  • Possible duplicate of [Using group by on multiple columns](https://stackoverflow.com/q/2421388/3404097)--In case your question has something to do with understanding how group by works in standard SQL & MySQL in the cases the standard allows. – philipxy Jun 05 '20 at 11:22
  • The linked Q&A explain what MySQL does in a certain mode in certain cases not allowed by standard SQL. Your code is not such a case. The answers describe stuff done by MySQL in those cases after the normal group by behaviour--which is not explained. PS I guess you can't see how the answers describe your group by. They don't, they're not trying to. But it's really not clear just what you want to ask. Use enough words & sentences & references to parts of exampes to say what you mean. Give a [mre] & put all that's needed to ask in your post, not at links. [ask] PS W3C is a poor resource. – philipxy Jun 05 '20 at 11:35
  • @philipxy https://stackoverflow.com/a/2421441/8732285 Yeah this is basically what I want to accomplish. I dont understand what I am doing wrong, to me it looks like Im applying the same logic as this answer does. Oo – Narktor Jun 05 '20 at 12:45
  • I'd like to help, but I don't know what you want, the example result is tiny & not very enlightening to guess from & you don't give a MRE & you don't clearly explain how output is a function of input. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. Isolate the first subexpression with a result you don't expect, say what you expected & why. But first learn what grouping does on known simple input--joins are irrelevant--not a complex subexpression that you might have wrong. – philipxy Jun 05 '20 at 20:05

2 Answers2

1

Your second last paragraph is just wrong. IF you run this

SELECT  o.orderid,suppliername,p.productname
FROM Suppliers s
JOIN Orders o
ON o.shipperid = s.supplierid
JOIN OrderDetails od
ON od.orderid = o.orderid
JOIN Products p 
ON p.productid = od.productid
where o.orderid = 10300

here https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_count

you get this 

orderid suppliername                productname
10300   New Orleans Cajun Delights  Louisiana Hot Spiced Okra
10300   New Orleans Cajun Delights  Scottish Longbreads

so the output from your query is correct there should only be one row produced in the result

P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • This doesnt really help, your query doesnt aggregate or group anything. My goal wasn't solely to retrieve the records matching the SELECT statement, I wanted a COUNT for the number of products belonging to each supplier concerned with the order. – Narktor Jun 05 '20 at 12:42
  • The query proves that there is only 1 supplier for this orderid. – P.Salmon Jun 05 '20 at 12:51
  • Then either there's something wrong with the DB or with your query. If you look it up manually: Order-ID 10300 holds products with the IDs 66 and 68. These products have two suppliers, by ID 2 and 8. In suppliers table, these are: New Orleans Cajun Delights (ID 2) and Specialty Biscuits, Ltd. (ID 8) – Narktor Jun 05 '20 at 12:56
  • 1
    The query is wrong(maybe) here ON o.shipperid = s.supplierid there is only 1 shipper (2) - I see you have rewritten properly – P.Salmon Jun 05 '20 at 13:41
0

I found the answer myself:

SELECT  o.orderid, s.suppliername, COUNT(p.productname) AS numberOfProducts
FROM Orders o
JOIN OrderDetails od
ON o.orderid = od.orderid
JOIN Products p
ON p.productid = od.productid
JOIN Suppliers s 
ON s.supplierid = p.supplierid
GROUP BY o.orderid, s.suppliername
HAVING o.orderid = 10300;

The mainissue was that ON o.shipperid = s.supplierid had to be ON s.supplierid = p.supplierid

friendly users on SO helped me out on that :)

Narktor
  • 977
  • 14
  • 34
  • 2
    The order of the joins makes no difference here. 5 x 3 is the same as 3 x 5. – Strawberry Jun 05 '20 at 19:16
  • 1
    The reason this is different is that you changed `ON o.shipperid = s.supplierid` to `ON s.supplierid = p.supplierid`, ie you used to ask for rows where a shipper was a supplier. Also you have misconceptions about certain order mattering. Let's ignore column order of results. Then:`x join y on c`=`y join x on c`=`x join y where c`; `x join y on c join z on d`=`x join y join z on c and d`=`x join y join z where c and d`; & if y & z are mentioned as join argument before in c & d, the latter 3=`x join y on d join z on c`. See a published textbook or DBMS manual to learn how the language works. – philipxy Jun 05 '20 at 20:32