MySQL query query

December 6, 2006 10:21 AM

Help with troublesome MySQL query?

I have a single table of phone call logs, and I'm trying to create a query to pull all of the callers who have called more than X times within a given time period. I'm running MySQL 5.0.24 on Windows. I've created the following, which partly works:

SELECT
Count(callerid) as occurrences,
callerid,
callername,
memo
FROM
calls
WHERE
date > '2006-12-01'
GROUP BY
callerid
HAVING
occurrences > 2
ORDER BY
occurrences DESC;

The problem is that this query only returns the first record for each individual caller, and I want all of them. What am I missing here?

posted by cdavis to Computers & Internet (8 answers total)

your "group by" will only return one record per caller.

Try doing a subselect of the callerids with more than 2 occurrences, then getting records for all those in the target period.

Something like:

SELECT
callerid,
callername,
memo
FROM
calls
WHERE
date > '2006-12-01'
AND callerid in

(SELECT
callerid,
Count(callerid) as occurrences,
FROM
calls
WHERE
date > '2006-12-01'
GROUP BY
callerid
HAVING
occurrences > 2)

ORDER BY
callerid;

(not tried, obviously, and I can't remember which version of MySQL allows subselects)
posted by Artful Codger at 10:33 AM on December 6, 2006

You're asking for different things in your two comments. Your first comment asks for a *list* of callers who have called more than x times, which is what you're getting. Your last comment asks for *all of the records* from callers who have called more than x times, which is a different request.

I would use a subselect for this, although there's probably a more elegant way:

SELECT *
  FROM calls
  WHERE callerid IN (
    SELECT callerid
      FROM calls
      (WHERE other criteria)
      GROUP BY callerid
      HAVING count(*) > 2
  )

posted by pocams at 10:34 AM on December 6, 2006

Best answer: Neither of the above is doable, because MySQL requires the subquery to only return one column. This will work:

SELECT
(SELECT count(callerid) FROM calls c2 where c2.callerid=calls.callerid) AS occurrences,
callerid,
callername,
memo
FROM
calls
WHERE
date > '2006-12-01'
HAVING
occurrences > 2
ORDER BY
occurrences DESC;

The problem is that this query only returns the first record for each individual caller, and I want all of them. What am I missing here?

That's what GROUP BY does. One row per callerid value.
posted by cillit bang at 10:48 AM on December 6, 2006 [1 favorite]

My SQL is a but rusty, but why won't this work:

SELECT
Count(callerid) as occurrences,
callerid,
callername,
memo
FROM
calls
WHERE
date > '2006-12-01'
HAVING
occurrences > 2
ORDER BY
callerid,
occurrences DESC;
posted by nkyad at 10:59 AM on December 6, 2006

cillit: The way I've written the subquery, it does return only one column, and works fine, at least in my test 4.1.11. I do like your method of tagging each row with its count, which I wasn't able to do.

On preview: nkyad, you can't use aggregate functions like COUNT() without a GROUP BY.
posted by pocams at 11:03 AM on December 6, 2006

In my query:

(SELECT count(callerid) FROM calls c2 where c2.callerid=calls.callerid) AS occurrences,

Should be:

(SELECT count(callerid) FROM calls c2 WHERE
date > '2006-12-01' AND c2.callerid=calls.callerid) AS occurrences,

(adding the date here means only certain records are included in the count)

The way I've written the subquery, it does return only one column

You're right.
posted by cillit bang at 11:37 AM on December 6, 2006

Best answer: Sorry should be:

(SELECT count(callerid) FROM calls c2 WHERE
c2.date > '2006-12-01' AND c2.callerid=calls.callerid) AS occurrences,
posted by cillit bang at 11:38 AM on December 6, 2006

Response by poster: Cillit bang's (updated) solution worked. Thanks!
posted by cdavis at 7:00 AM on December 7, 2006