CODEWITHSUNDEEP
mysqlsql
aristotll
aristotll

Reputation: 9177

[HY000][1111] Invalid use of group function

I have searched a lot ,but none of other questions with error 1111 solves my problem. My needs are to count the distinct phone number of some id The following code works:

SELECT
  a.id_borrow_application,
  count(DISTINCT c.phone_no) CVG_CALL_OUT_COUNTS_6M
FROM t_snow_borrow_application_id a
  JOIN t_snow_call_mobile b
  JOIN t_snow_call_record_201612 c ON
                                     (
                                       a.id_borrow_application = b.id_borrow_application
                                       AND b.id = c.id_call_mobile
                                       )
WHERE c.call_type = 0
GROUP BY a.id_borrow_application;

But when I want to write 4 similar queries together,the error in title happens.

[HY000][1111] Invalid use of group function 

SELECT
  a.id_borrow_application,
  sum(CASE WHEN call_type = 0
    THEN count(DISTINCT c.phone_no)
      ELSE 0 END) CVG_CALL_OUT_COUNTS_6M,
  sum(CASE WHEN call_type = 0 AND c.days <= 30
    THEN count(DISTINCT c.phone_no)
      ELSE 0 END) CVG_CALL_OUT_COUNTS_1M,
  sum(CASE WHEN call_type = 1
    THEN count(DISTINCT c.phone_no)
      ELSE 0 END) CVG_CALL_IN_COUNTS_6M,
  sum(CASE WHEN call_type = 1 AND c.days <= 30
    THEN count(DISTINCT c.phone_no)
      ELSE 0 END) CVG_CALL_IN_COUNTS_1M
FROM t_snow_borrow_application_id a
  JOIN t_snow_call_mobile b
  JOIN t_snow_call_record_201612 c ON
                                     (
                                  a.id_borrow_application = b.id_borrow_application
                                   AND b.id = c.id_call_mobile
                                       )
GROUP BY a.id_borrow_application;

Do I have to write 4 queries?

Upvotes: 0

Views: 419

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You are nesting aggregate function which is not allowed in MySQL.

You don't actually need the sum function for count distinct phone_nos for different conditions. Take the count (distinct outside the case and remove sum function and else clause of the case.

Try this:

select a.id_borrow_application,
    count(distinct case when call_type = 0 then c.phone_no end) CVG_CALL_OUT_COUNTS_6M,
    count(distinct case when call_type = 0
                and c.days <= 30 then c.phone_no end) CVG_CALL_OUT_COUNTS_1M,
    count(distinct case when call_type = 1 then c.phone_no end) CVG_CALL_IN_COUNTS_6M,
    count(distinct case when call_type = 1
                and c.days <= 30 then c.phone_no end) CVG_CALL_IN_COUNTS_1M
from t_snow_borrow_application_id a
join t_snow_call_mobile b
join t_snow_call_record_201612 c on (
        a.id_borrow_application = b.id_borrow_application
        and b.id = c.id_call_mobile
        )
group by a.id_borrow_application;

Upvotes: 1

Related Questions