SQLSTATE[42000]聚合函数错误 - SQLSTATE[42000] Error on aggregation functions

你好，我在SQL查询中出现错误，无法找出问题所在。以下是在Barmar的帮助下，到目前为止的查询。

 $query = "SELECT c.*, count(s.curso_id) as count, SUM(IF(s.status = 'aprobado', 1, 0)) AS count_approved , SUM(IF(s.status = 'cupolleno', 1, 0)) AS count_cupolleno
    , SUM(IF(s.status = 'cancelado', 1, 0)) AS count_cancelado, SUM(IF(s.status = 'noacion', 1, 0)) AS count_noacion, SUM(IF(s.status = 'ama_de_casa', 1, 0)) AS count_ama_de_casa
    , SUM(IF(s.status = 'cliente_externo', 1, 0)) AS count_cliente_externo
    FROM cursos_modulos AS c
    LEFT JOIN subscriptions AS s ON s.curso_id = c.id
    LEFT JOIN users AS u ON u.userID = s.user_id GROUP BY c.id WHERE 1";
                if (!empty($id)) { $query .= " AND c.id = '$id'"; }
                if (!empty($ciudad)) { $query .= " AND c.ciudad = '$ciudad'"; }
                if (!empty($tipo)) { $query .= " AND c.tipo = '$tipo'"; }
                if (!empty($titulo))  { $query .=" AND c.titulo = '$titulo'"; }
                if (!empty($status))  { $query .= " AND c.status = '$status'"; }
 $paginate = new pagination($page, $query, $options);

我得到的错误信息如下:

致命错误:未捕获的异常'PDOException'带有消息'SQLSTATE[42000]: Syntax error or access violation: 1064SQL语法错误;请查阅与您的产品相对应的手册MySQL服务器版本的正确语法在'WHERE 1 AND '附近使用c.id = '1' LIMIT 0, 30' at line 6' inE:'xampp'htdocs'admin'class'分页。php:376E: ' xampp '根类' admin ' ' pagination.php (376):PDOStatement -> execute () # 1E: ' xampp '根类' admin ' ' pagination.php (202):分页-> excecute_query () # 2E: ' xampp '根类' admin ' ' pagination.php (162):pagination->run(1， 'SELECT c.， cou…'， Array) #3E: ' xampp '根' admin ' search.php (146):分页-> __construct(1,选择c。,再……'， Array) #4 {main}在E:'xampp'htdocs'admin'class' paginering .php中抛出376

group by条款应该在where条款之后。例如:

$query = "SELECT c.*, count(s.curso_id) as count, SUM(IF(s.status = 'aprobado', 1, 0)) AS count_approved , SUM(IF(s.status = 'cupolleno', 1, 0)) AS count_cupolleno
, SUM(IF(s.status = 'cancelado', 1, 0)) AS count_cancelado, SUM(IF(s.status = 'noacion', 1, 0)) AS count_noacion, SUM(IF(s.status = 'ama_de_casa', 1, 0)) AS count_ama_de_casa
, SUM(IF(s.status = 'cliente_externo', 1, 0)) AS count_cliente_externo
FROM cursos_modulos AS c
LEFT JOIN subscriptions AS s ON s.curso_id = c.id
LEFT JOIN users AS u ON u.userID = s.user_id WHERE 1";
            if (!empty($id)) { $query .= " AND c.id = '$id'"; }
            if (!empty($ciudad)) { $query .= " AND c.ciudad = '$ciudad'"; }
            if (!empty($tipo)) { $query .= " AND c.tipo = '$tipo'"; }
            if (!empty($titulo))  { $query .=" AND c.titulo = '$titulo'"; }
            if (!empty($status))  { $query .= " AND c.status = '$status'"; } 
$query .= " GROUP BY c.id";

where 1为您做什么?试着干掉它。

不会抛出1064错误:

create table cursos_modulos
(   id int not null
);
create table subscriptions
(   curso_id int not null,
    user_id int not null,
    status varchar(100) not null
);
create table users
(   userID int not null
);
SELECT c.id,
count(s.curso_id) as count, 
SUM(IF(s.status = 'aprobado', 1, 0)) AS count_approved, 
SUM(IF(s.status = 'cupolleno', 1, 0)) AS count_cupolleno, 
SUM(IF(s.status = 'cancelado', 1, 0)) AS count_cancelado, 
SUM(IF(s.status = 'noacion', 1, 0)) AS count_noacion, 
SUM(IF(s.status = 'ama_de_casa', 1, 0)) AS count_ama_de_casa,
SUM(IF(s.status = 'cliente_externo', 1, 0)) AS count_cliente_externo
FROM cursos_modulos AS c
LEFT JOIN subscriptions AS s ON s.curso_id = c.id
LEFT JOIN users AS u ON u.userID = s.user_id 
GROUP BY c.id