I'm new to SQL so appreciate any help on this. I've been using an inner join and where clause to get all the unique Jedi who were active for 3 months in the Republic Area (Republic Table) and then determine how many of these Jedi were part of the Rebel Alliance (Rebel Table).

My query does execute but the data make me doubt I'm doing it right. Thank you for your help on this.

SELECT COUNT (DISTINCT republic.jedi_id)
FROM republic
INNER JOIN rebel ON republic.jedi_id = rebel.jedi_id
WHERE republic.date>='2020-01-01' AND republic.date <='2020-03-31'

CodePudding user response：

MySQL interprets your code as two commands and produces an error because DISTINCT is at the wrong place.

To be precise:

• COUNT(DISTINCT invokes the Function COUNT with distinct rows
• COUNT (DISTINCT is treated as two separate commands and DISTINCT belongs at the beginning direct after the SELECT, which gives an error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT republic.jedi_id) FROM republic INNER JOIN rebel ON republic.jedi_id = ' at line 1

Rewrite your code to:

SELECT COUNT(DISTINCT republic.jedi_id)
FROM republic
INNER JOIN rebel ON republic.jedi_id = rebel.jedi_id
WHERE republic.date>='2020-01-01' AND republic.date <='2020-03-31'

CodePudding user response：

Query is pretty much correct. Distinct is needed if there are than one Jedi with same name is any of the table.

SELECT COUNT republic.jedi_id
FROM republic
INNER JOIN rebel ON republic.jedi_id = rebel.jedi_id
WHERE republic.date>='2020-01-01' AND republic.date <='2020-03-31'