1

I am learning SQL. A task is to write the expression 

SELECT MIN(Price), ProductName 
FROM Products;

The editor https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in and takes an image of the results.

My teacher says that it's working fine for him. When I do it, SQL returns

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

The answers I found included changing the settings of the program (which I can't do) or doing things that I haven't learned yet. Why does the expression return different things for me and my teacher and what can I do to change this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Quintium
  • 358
  • 2
  • 11
  • 3
    You can't mix aggregate functions and plain column references in the select list without having a GROUP BY clause. – jarlh May 27 '20 at 07:49
  • 3
    Well, it's invalid SQL. Apparently your teacher uses a DBMS product that doesn't care about the SQL standard and chooses to run invalid SQL and return random results. If your teacher thinks that's valid SQL, that doesn't really shed a positive light on the knowledge of the teacher –  May 27 '20 at 07:49
  • because you can not GROUP the Product name without specifying HOW you want to group it. Do you want `SUM(ProductName)` or `LIST(ProductName` or `AVG(ProductName)` ? What do *you want* to do with `ProductName`, how *do you plan* to use it? You can either *fold* (using term from functional programming) many values into one, or you should designate it as an anchor, separating one group from other groups. – Arioch 'The May 27 '20 at 07:50
  • To be fair it does work in that link without errors for me too. – Guy Incognito May 27 '20 at 07:50
  • 1
    @GuyIncognito: that doesn't change the fact that it's invalid (standard) SQL and no teacher with a bit of self-respect should teach that. –  May 27 '20 at 07:51
  • 1
    @GuyIncognito the link says it uses any SQL that is built into your WWW browser. You may make a guess and try `SELECT sqlite_version()` there, then read about possible deviations from a standard – Arioch 'The May 27 '20 at 08:16
  • Not arguing that it isn't wrong, but the OP says that it doesn't work for them on that specific web site when it does work on that web site. But if it uses the browser's db engine then maybe it's because of a different browser. – Guy Incognito May 27 '20 at 08:19
  • @R.B. you may try severalk different SQL engines, not merely your WWW browser one, at https://dbfiddle.uk/ While there ARE non-standard ways to make a SOMEWHAT RESEMBLING your query, you better start with doing a plain SQL standard query that works in EVERY possible SQL engine. Play by rules first, learn ugly hacks to break the rules later. – Arioch 'The May 27 '20 at 08:19
  • @GuyIncognito that broken query works to me too, in Falkon browser that bundles 3.25 SQLite, your browser may use some different SQLite build or flags. – Arioch 'The May 27 '20 at 08:22
  • @GuyIncognito I just checked, it works on Chrome but doesn't on Firefox and Edge. – Quintium May 27 '20 at 08:22
  • @R.B. funyn thing, that there are TWO Edge browser. Legacy Edge, shipped with Windows 10, and Modern Edge, which is Chrome with facelifting :-) They probably bundle different SQLite versions, or maybe have different settings for it (if SQLite has settings, i don't know much of that engine) – Arioch 'The May 27 '20 at 08:26
  • Easy demonstration about *instability* of the query in question. First run `insert into products values (100, 'Geitost-Promo', 15,4, '600 g', 2.5)` then issue `SELECT MIN(Price), ProductName FROM Products` vs normative `SELECT Price, ProductName FROM Products WHERE PRICE = (select MIN(Price) from products)` - you see a data being lost by the fisrt query. Then try `SELECT Price, ProductName FROM Products ORDER by Price ASC LIMIT 1` which is a better way of doing the first query, but `LIMIT 1` is also non-standard SQL and it also may randomly miss data. – Arioch 'The May 27 '20 at 10:08

4 Answers4

1

Avoid mixing plain columns with aggregate functions for derived columns. For testing, You could use:

SELECT MIN(Price) AS PRICE 
FROM Products;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JaredKarl
  • 87
  • 1
  • 11
1

Because you can not GROUP many different values for the Product Name column without specifying HOW exactly do you want to group it.

Do you want to get a SUM(ProductName) or LIST(ProductName or AVG(ProductName) or something else?

What do you want to do with that ProductName, how do you plan to use that column?

You can either fold (using term from functional programming) many values into one 

SELECT MIN(Price), MAX(ProductName) FROM Products

Or you should designate it as an anchor, separating one group from other groups.

SELECT MIN(Price), ProductName 
FROM Products
GROUP BY ProductName

When you do aggregating - every column of your query have to

  • either be unique per-group value, telling one group from adjacent groups (then it should be listed in GROUP BY clause)
  • or be aggregated by an aggregating function of your choice through every values group (then you should specify the function you want to be used)

Another concern is using ProductName as a key field.

That usually is bad idea for many reasons (three at least). Usually you would want an integer numeric ID and a separate table, that holds names and other attributes for every synthetic ID of a product.

See also:

Arioch 'The
  • 15,799
  • 35
  • 62
  • It should return the smallest price as a column and the name of the product with that price as a column. How do I do that? – Quintium May 27 '20 at 08:05
  • @R.B. what you gonna do if there are several products with THE SAME minimal price? Like those "everything for $2" shops? – Arioch 'The May 27 '20 at 08:06
  • I guess it shoud return all `ProductName`s with that price. – Quintium May 27 '20 at 08:09
  • You request (about making one line) is not possible to do in general sense. You have to `JOIN` two queries. One query should get the one minimum price over ALL products (thus, lacking any other product attribute reference), another should select many (1 or more) products, which price is equal to a given value. Write both queries one at time. Then read your textboook about joins, then join those two queries into one composite query, so it would enlist products wiuth prices equal to the minumum price. – Arioch 'The May 27 '20 at 08:09
  • I'm not quite sure how to do this, I tried `SELECT Products.Price, Products.ProductName FROM Products INNER JOIN Products WHERE Products.Price = MIN(Products.Price);` but it didn't work. – Quintium May 27 '20 at 08:25
  • @R.B. you have to write TWO separate queries first and make them work one by one. Also, perhaps i was wrong about JOINs, perhaps what you need is `Correlated sub-query` as in https://stackoverflow.com/questions/17268848 – Arioch 'The May 27 '20 at 08:38
  • @R.B. one more correlated subquery example in https://stackoverflow.com/questions/390534 – Arioch 'The May 27 '20 at 08:42
  • Thanks! This actually works! However I might just complete the task with Chrome and let the teacher know that his code isn't accepted everywhere. – Quintium May 27 '20 at 08:48
  • @R.B. you have *two* things named `Products` in that query. You can need to provide an alias to distinguish them: `SELECT P.Price, P.ProductName FROM Products AS P INNER JOIN Products WHERE P.Price = MIN(Products.Price);` – Caleth May 27 '20 at 09:22
  • @Caleth no, this query would not work in any standard SQL, you can not use MIN outside of an explicit aggregating query. And I was wrong about using JOIN to compose queries. Granted, ecery engine has its own quirks and bugs that go beyond standard, but that is not the normal and universal ways. – Arioch 'The May 27 '20 at 09:41
  • @R.B. i think your teacher actually showed you some similar but different queries. Or he should had mentioned he makes queries tightly bound to one specific engine. You may google for a rant named like "MySQL 3 gotchas" for an example of such SQL abuses. Ugly hacks can be quite useful, if you enforce safety checks they will never be tried in any different invironment today or in any remote future. / If you would have an argument with a teacher (think twice if you should) i repeat my above idvice to go DB Fiddle and try it with ALL SQL engines there. – Arioch 'The May 27 '20 at 09:45
  • 1
    @R.B. some people might feel very insecure about their faults, especially when they were supposed to have position of authority over you. Showing their faults - even in private and god forbig in public - can trigger their irrational aggression. Though other people would feel grateful for fixing their fauls. I do not know what kind is your teacher. But if you want to bring this out - then impersonal " would not permit it" sounds better than "me, a student, throws SQL book at ya!" – Arioch 'The May 27 '20 at 09:48
  • @R.B. i already told you yesterday - go and try yourself - http://dbfiddle.uk/. If you want to prove teacher wrong You can not just say "some anonymous pal in some forum assured me that..." // additionally do that `INSERT` stmt i wrote yesterday, make the "all for $2" shop environment and show that even when the original query returns no error, it still does not return correct data. – Arioch 'The May 28 '20 at 09:24
  • 1
    @R.B. It is hard to argue with "He also said that his syntax is correct because if it wasn't, it wouldn't work anywhere." :-) It is like claiming that driving drunk is correct because would it nor then every single booze-powered ride would had ended in a crash - and u can't deny most end perfectly ok. https://blog.codinghorror.com/the-works-on-my-machine-certification-program/ You might gossip with like-minded students, or with like-minded college administration, if there are any, but the course he reads to you brings him salary and brings the college profits, so why would they change it? – Arioch 'The May 28 '20 at 11:09
1

Your query would work in MySQL (old versions) or SQLite. But you don't want an aggregation query. In this case, ORDER BY and LIMIT does what you want:

SELECT p.Price, p.ProductName 
FROM Products p
ORDER BY p.Price
LIMIT 1;

Your teacher should know this and should not be teaching the broken syntax. It is not SQL standard and it does not work in most databases.

Your syntax is broken because the GROUP BY and SELECT clauses are incompatible -- you have unaggregated columns in the SELECT (I think other answers explain this).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @Arioch'The . . . Both this and the version in the question return one row. So, that would be an entirely different question. – Gordon Linoff May 28 '20 at 10:36
0

Obviously you and your teacher have different settings or different versions of the database program. If it is my sql. MySQL offers a setting ONLY_FULL_GROUP_BY You may read more at https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html about how to set it for your need. If you are unable to change the settings, there may not be a way around it.

Vikash Agrawal
  • 109
  • 4
  • The link clearly says it is not MySQL or any fixed engine. It is whatever engine your WWW bundles. And mysql-ish `select @@version` would most probably NOT work by that link, go and try. – Arioch 'The May 27 '20 at 10:10
  • It does not specify an engine but I am sure they are using one and the problem description indicate that it is my sql. I have tried to answer the question towards the end of the request _Why does the expression return different things for me and my teacher and what can I do to change this?_ select @@version is ms sql, that wont work in mysql anyway. – Vikash Agrawal May 27 '20 at 12:03
  • Yes, they are using one, and they clearly say which one - the one provided by your www browser. Does your WWW browser has MySQL inside it, what do you think ? // Google says it will work, and so do say https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=6d66d7c7e992c9e6b2ac8ba27050a258 and https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6d66d7c7e992c9e6b2ac8ba27050a258 and https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=6d66d7c7e992c9e6b2ac8ba27050a258 – Arioch 'The May 28 '20 at 09:32