CODEWITHSUNDEEP
mysql
Pankaj
Pankaj

Reputation: 81

Invalid use of group function - MySQL

This error occurs while I run the select query, please advise.

Error No : 1111
Error : Invalid use of group function

Query: 

SELECT cm.contactid, cm.firstname, cm.surname, cm.mobile, COUNT( * ) total
FROM sales_master as sm, contact_master as cm
WHERE sm.contactid = cm.contactid 
AND cm.mobile != '' AND orderdate>='2012-12-18' AND orderdate<='2013-03-18' 
GROUP BY sm.contactid 
HAVING COUNT(*) >= 1 
ORDER BY COUNT(*) DESC, cm.firstname ASC

Upvotes: 2

Views: 5461

Answers (2)

Rahul
Rahul

Reputation: 5636

There will be two reason:

1) Version compatibility Problem

2) Your query syntax is not ok,I am sending you the solution of second option. For Version compatibility Problem you must go to the link mentioned by some one on comment. You have to use subquery concept for that.

Modify your query something like that:

SELECT  sm.contactid ,cm.contactid as contactid , cm.firstname as firstname, cm.surname as surname , cm.mobile as mobile , COUNT( * ) total
FROM    (
            SELECT  sm.contactid,contactid , firstname,surname ,mobile ,total
            FROM    sales_master as sm, contact_master as cm
            WHERE sm.contactid = cm.contactid 
            AND cm.mobile != '' AND orderdate>='2012-12-18' AND orderdate<='2013-03-18' 
            GROUP BY sm.contactid 
         ) q
GROUP BY sm.contactid HAVING COUNT(*) >= 1 
ORDER BY COUNT(*) DESC, cm.firstname ASC

I am not sure that i made exact query or not but concept is some thing like that means "Subquery"...

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Change your query like this. Use join instead of cartisian. If type of cm.orderdate is DAte than remove DATE() from the below query. And use alias total in GROUP BY and ORDER BY instead of Count(*) .

SELECT
  cm.contactid,
  cm.firstname,
  cm.surname,
  cm.mobile,
  COUNT(cm.contactid)    total
FROM sales_master as sm
  LEFT JOIN contact_master as cm
    ON sm.contactid = cm.contactid
WHERE 
    AND cm.mobile != ''
    AND DATE(cm.orderdate) >= '2012-12-18'
    AND DATE(cm.orderdate) <= '2013-03-18'
GROUP BY cm.contactid
HAVING total >= 1
ORDER BY total DESC, cm.firstname ASC

Upvotes: 1

Related Questions