Group By query in MySQL

[MTInc]

I am converting my web app to MySql and I have been stumped by a Group By query that worked in the old DBMS (Paradox) but not in MySQL.
I haven't seen any good examples of a Group By query in MySQL featuring multiple tables or more than on Join clause. This has left me casting about in the dark with no luck. Even seeing a good example of a multi-table Group By query that works in MySQL would be a big help.


SELECT SUM(CP.Points) AS TotalPoints, COUNT(CP.Points) AS RecCount, MIN(CP.Rank) AS HiRank,
P.PubID, P.Title,
A.AuthorName,
L.ImprintlName
FROM PubList AS CP, Publication AS P, Author AS A,Imprint AS L
INNER JOIN PubList ON P.PubID = CP.PubID
INNER JOIN Author ON P.AuthorID = A.AuthorID
INNER JOIN Imprint ON P.ImprintID = L.ImprintID


GROUP BY
P.PubID, D.Title,
A.AuthorName,
L.ImprintlName

HAVING CP.TypeID = #intTypeID#
AND CP.SampleDate = #CreateODBCDate(SampleDate)#


ORDER BY SUM(CP.Points) DESC, COUNT(CP.Points) DESC, MIN(CP.Rank) ;

****************************
I get this error:

ODBC Error Code = S1000 (General error)

[MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-debug]Invalid use of group function

Any help is very much appreciated.

 

[KamranFeroz]

This might be bcoz ur using an alis "d" for table that is not visible in your "from" clause in "select" statement.

Check this out.

Best of luck.

 

[swampBoogie]

SELECT SUM(CP.Points) AS TotalPoints, COUNT(CP.Points) AS RecCount, MIN(CP.Rank) AS HiRank,
P.PubID, P.Title,
A.AuthorName,
L.ImprintlName
FROM PubList as CP INNER JOIN PUBLICATION as P
ON P.PubID = CP.PubID
INNER JOIN Author AS A 
ON P.AuthorID = A.AuthorID
INNER JOIN Imprint L 
ON P.ImprintID = L.ImprintID
where CP.TypeID = #intTypeID#
AND CP.SampleDate = #CreateODBCDate(SampleDate)#
GROUP BY
P.PubID, P.Title,
A.AuthorName,
L.ImprintlName
ORDER BY SUM(CP.Points) DESC, COUNT(CP.Points) DESC, MIN(CP.Rank) ;

It is sufficient to the name the tables in the join clause.