What is the reason that the following two queries give wildly different results?
MariaDB [mydatabase]> SELECT COUNT(DISTINCT(`price`)) FROM `products`; --Good
+--------------------------+
| COUNT(DISTINCT(`price`)) |
+--------------------------+
| 2059 |
+--------------------------+
1 row in set (0.01 sec)
MariaDB [mydatabase]> SELECT COUNT(DISTINCT('price')) FROM `products`; --Bad
+--------------------------+
| COUNT(DISTINCT('price')) |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.01 sec)
I've googled around for an explanation of the difference between backticks and apostrophes (aka. single quotes), but I am unable to find any indication as to why they would be interpreted differently for a column name like in the above.
Is it that the single-quoted string in the latter query is actually not interpreted as a column name, but just as an arbitrary string literal, of which there could be said to be "1"? If so, it ain't easy to find any pages expounding on this meaning of the apostrophe.
The single quote should be used in your writing instead of using the back quote. In other words, use a single quote instead of a back quote when writing the apostrophe in words like "don't" and "I'm." When used as an accent, the grave accent indicates pitch, stress, or tone of a letter.
The backtick ` is a typographical mark used mainly in computing. It is also known as backquote, grave, or grave accent. The character was designed for typewriters to add a grave accent to a (lower-case) base letter, by overtyping it atop that letter.
As you may have understood now, there is no real difference between using single quotes, double quotes, or backticks. You can choose one or multiple styles based on your preference. However, It is always good to stick to a single format throughout the project to keep it neat and consistent.
They are two entirely different punctuation symbols. Single quotes are limited to one real function in written U.S. English, which is to indicate a quotation within a quotation. Apostrophes, on the other hand, are used to denote possessive form and to indicate omission.
'price' (apostrophes or quotes) is a string. It never changes, so the count is always 1.
`price` (backtics) refers to the column price
. So it could be more than 1.
The inner parentheses are irrelevant. COUNT(DISTINCT price)
is the same as your backtic version.
SELECT COUNT(*) FROM tbl WHERE ...
is a common way to ask how many rows.SELECT foo, COUNT(*) FROM tbl GROUP BY foo
is a common way to ask how many rows for each distinct value of foo
.SELECT foo, COUNT(foo) FROM tbl GROUP BY foo
is the same as above, but does not count rows where foo IS NULL
.SELECT DISTINCT ... GROUP BY ...
is a nonsense statement. Either use DISTINCT or use GROUP BY.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With