Query works in SQLite3, but it is a syntax error in Microsoft SQL Server [closed]-CodePudding

That query is:

SELECT 100.*COUNT(DISTINCT names_of_numbers.language_name)/variables.value
    AS "Percentage of Indo-European languages in which the words for 2 and 10 start with the same letter."
    FROM (names_of_numbers as numbers1),names_of_numbers,languages,variables
    WHERE variables.variable_name='Number of Indo-European languages' and numbers1.language_name=names_of_numbers.language_name
         and names_of_numbers.language_name=languages.language_name and languages.language_family='Indo-European' and
         substr(numbers1.word,1,1)=substr(names_of_numbers.word,1,1) and numbers1.value=2 and names_of_numbers.value=10;

Microsoft SQL server tells me there is a syntax error near ) in the third line. What is going on here? How can I make that query in standard SQL?

CodePudding user response：

You get that error because of the parentheses here:

(names_of_numbers as numbers1)

Remove them.

Also, you must change SUBSTR() to SUBSTRING() which is SQL Server's equivalent function, although for your case LEFT() would also work.

Also, since you are aggregating with COUNT() the column variables.value will not be allowed by SQL Server (SQLite allows it).
Instead use a subquery that returns variables.value.

Finally, use proper joins with aliases for all the tables and ON clauses:

SELECT 100.0 * COUNT(DISTINCT n2.language_name) / 
       (SELECT value FROM variables WHERE variable_name = 'Number of Indo-European languages') 
       AS [Percentage of Indo-European languages in which the words for 2 and 10 start with the same letter]
FROM names_of_numbers n1
INNER JOIN names_of_numbers n2 ON n2.language_name = n1.language_name
INNER JOIN languages l ON l.language_name = n2.language_name
WHERE n1.value = 2 AND n2.value = 10 AND SUBSTRING(n1.word, 1, 1) = SUBSTRING(n2.word, 1, 1) 
  AND l.language_family = 'Indo-European';