I have a query to run for coursework that has to show the number of books for each format.
Here is the table I am trying to query format can be (hardback, softback, audio, ecopy)
Here is the code I have tried, I am unaware how to expand to include all format types:
SELECT format, COUNT(format) FROM book WHERE format = 'hardback' OR 'softback' OR 'audio' OR 'ecopy'
I know this is incorrect but it only shows the hardback format and how many hardback books are included.
CodePudding user response：
I've decided to write an answer, because you must be wondering what happens in your query. I suppose you think it should either work or fail, but instead it works correctly for one format, but then it doesn't show any other. Why?
Your query works as follows: A where clause consists of a boolean expresssion. This can be multiple sub expressions combined with AND and OR. Your sub conditions are: format = 'hardback', 'softback', 'audio', 'ecopy'. Now, 'softback' is not really a condition. format = 'softback' would be. And here it gets weird. Rather then reporting a syntax error, MySQL wants a boolean, so it brashly converts your string.
It does so in two steps, because a string cannot be converted to boolean, but a string can be converted to number and a number to boolean. Hence the DBMS first converts your string 'softback' into a number. That should fail, but it doesn't obviously. This is the second time we expect a syntax error, but it isn't happening. MySQL takes the liberty to convert non-numeric strings into a zero.
Then MySQL converts this number into a boolean. In MySQL true = 1 and false = 0. So you have: WHERE format = 'hardback' OR false OR false OR false. Thus you only get 'hardback' books and count these. As there is just one format you select, it can be shown along with the count. I don't know whether MySQL really detects that this is valid, because the query only selects one format. I find it more likely that you are in MySQL's cheat mode (i.e. you haven't SET sql_mode = 'ONLY_FULL_GROUP_BY', which is a bad idea, because by working outside ONLY_FULL_GROUP_BY mode, you tell MySQL to let certain invalid queries pass and muddle through.) So MySQL sees there is a format to be selected, but it must be chosen which row to pick it from, and MySQL muddles through with silently applying ANY_VALUE(format).
What you want is an aggregation (count) with one result row per format. "Per ____" translates to GROUP BY ____ in SQL. So you want:
SELECT format, COUNT(*)
FROM book
GROUP BY format;
CodePudding user response：
You just need to add GROUP BY format at the end of the query.
CodePudding user response：
You have to write select query for each format, seperately!