ansi-sql
SQL query with distinct and sum
SELECT color, fruit, sum(rating) FROM medleys GROUP BY color, fruit Distinct is used to select distinct elements, nothing more, while you want to aggregate and for that you need GROUP BY and aggregation functions (SUM).
ANSI SQL Manual
Here’s the ‘Second Informal Review Draft’ of SQL:1992, which seems to have been accurate enough for everything I’ve looked up. 1992 covers most of the stuff routinely used across DBMSs.
What does SQL Select symbol || mean?
|| represents string concatenation. Unfortunately, string concatenation is not completely portable across all sql dialects: ansi sql: || (infix operator) mysql: concat ( vararg function ). caution: || means ‘logical or’ (It’s configurable, however; thanks to @hvd for pointing that out) oracle: || (infix operator), concat ( caution: function of arity 2 only ! ) … Read more
In MySQL, should I quote numbers or not?
MySQL is a lot like PHP, and will auto-convert data types as best it can. Since you’re working with an int field (left-hand side), it’ll try to transparently convert the right-hand-side of the argument into an int as well, so ‘9’ just becomes 9. Strictly speaking, the quotes are unnecessary, and force MySQL to do … Read more
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
this shows the different ways: — DB2 select * from table fetch first 10 rows only — Informix select first 10 * from table — Microsoft SQL Server and Access select top 10 * from table — MySQL and PostgreSQL select * from table limit 10 — Oracle select * from (select * from table) … Read more
Why does MySQL allow “group by” queries WITHOUT aggregate functions?
According to this page (the 5.0 online manual), it’s for better performance and user convenience.
How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
The IS DISTINCT FROM predicate was introduced as feature T151 of SQL:1999, and its readable negation, IS NOT DISTINCT FROM, was added as feature T152 of SQL:2003. The purpose of these predicates is to guarantee that the result of comparing two values is either True or False, never Unknown. These predicates work with any comparable … Read more
Why isn’t SQL ANSI-92 standard better adopted over ANSI-89?
According to “SQL Performance Tuning” by Peter Gulutzan and Trudy Pelzer, of the six or eight RDBMS brands they tested, there was no difference in optimization or performance of SQL-89 versus SQL-92 style joins. One can assume that most RDBMS engines transform the syntax into an internal representation before optimizing or executing the query, so … Read more