ORDER By problem

[maxpouliot]

Hi, the following query works :
SELECT DISTINCT(envois_employes.employes), employes.nomComplet,COUNT(envoi) AS totalEnvois,(SELECT COUNT(envoi) AS clics FROM envois_employes WHERE visite=true AND employes =employes.id) AS totalClics, totalClics/totalEnvois AS pourcentage FROM envois_employes INNER JOIN employes ON envois_employes.employes = employes.id GROUP BY envois_employes.employes, employes.nomComplet, employes.id

But if i add ORDER BY pourcentage i get the following error : ORDER BY clause (pourcentage) conflicts with DISTINCT

Can anybody tell me what do i have to do?

Thanks

Edit : Just found out that i don't need the DISTINCT, but my ORDER By pourcentage still doesn't work

Edit 2 :
SELECT envois_employes.employes, employes.nomComplet,COUNT(envoi) AS totalEnvois,(SELECT COUNT(envoi) AS totalClics FROM envois_employes WHERE visite=true AND employes =employes.id) AS CLIC, clic/totalEnvois AS pourcentage FROM envois_employes INNER JOIN employes ON envois_employes.employes = employes.id GROUP BY envois_employes.employes, employes.nomComplet, employes.id ORDER BY "pourcentage"

Does not give me an error but doesn't really Order By

[Fumigator]

Are you using actual double-quotes around pourcentage in the order by? That's wrong... remove the quotes.

[maxpouliot]

It returns an error if i don'T put quotes (single or double)

[Fumigator]

That same error or another one? And let me just tell you, when you put it in quotes, it makes it a literal string so you are simply saying "order each row using this literal value" which is completely useless, because every row will contain that same value.

[maxpouliot]

here's my current query :
SELECT DISTINCT(envois_employes.employes), employes.nomComplet,COUNT(envoi) AS totalEnvois,(SELECT COUNT(envoi) AS clics FROM envois_employes WHERE visite=true AND employes =employes.id) AS totalClics, totalClics/totalEnvois*100 AS pourcentage FROM envois_employes INNER JOIN employes ON envois_employes.employes = employes.id GROUP BY envois_employes.employes, employes.nomComplet, employes.id ORDER BY pourcentage

and the error i now have
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] ORDER BY clause (pourcentage) conflicts with DISTINCT.

[Fumigator]

This isn't MySQL? :confused:

[maxpouliot]

You're right, i'm using MS Access as a database. Does this really change something?

[Fumigator]

You're right, i'm using MS Access as a database. Does this really change something?

Yes of course :p

Each DBMS does things differently. There is a SQL standard but no one follows it exactly. Not to mention I've never seen this DISTINCT/ORDER BY conflict in MySQL (or Oracle or DB2 for that matter).

It looks like your problem is the temp table cannot be used in the order by. Perhaps if you split this query up into two queries you'll have better luck?

[Old Pedant]

I don't think this would work in MySQL or SQL Server or any other DB.

You are doing that
(SELECT COUNT(envoi) AS clics FROM envois_employes WHERE visite=true AND employes =E.id) AS totalClics
as a single field item in the SELECT list and then trying to *refer* to that same field item in the next field via
totalClics/totalEnvois*100 AS pourcentage

And the same thing with
COUNT(envoi) AS totalEnvois

Nope. Not legal. You can't use an aliased field name as part of an expression in creating another field.

You have to *repeat* the expressions, thus:

SELECT EE.employes, E.nomComplet, COUNT(envoi) AS totalEnvois,
(SELECT COUNT(envoi) AS clics FROM envois_employes WHERE visite=true AND employes=E.id) AS totalClics,
100.0 * (SELECT COUNT(envoi) AS clics FROM envois_employes WHERE visite=true AND employes=E.id)/COUNT(envoi) AS pourcentage
FROM envois_employes AS EE INNER JOIN employes AS E
ON EE.employes = E.id
GROUP BY EE.employes, E.nomComplet
ORDER BY 5

Notice that I *also* had to use "ORDER BY 5". That's because Access doesn't permit you to use an alias ("AS name") from the SELECT in the ORDER BY if the alias refers to an expression (as pourcentage is, of course).

Now, I'm *still* not sure poor little baby Access is capable of doing this. You are pushing its envelope, quite a bit.

But you can try it.