MySQL错误#1111 -组函数的无效使用

Question

是的，这是一项任务。因此，任务是输出两列的“名”和“姓”的条件：

-A u (B∩-C∩-(A∩-( B)

A:周一和周五没有购物的所有消费者(time_by_day.the_day) 所有购买“非消费品”的消费者(product_class.product_family) C:所有一次购买超过10件商品(sales_fact_1997.unit_sales)的消费者(sales_fact_1997.time_id) D:加拿大女性消费者(consumer.gender，consumer.country)

这就是我到目前为止所得到的

SELECT
    c.fname,
    c.lname
FROM
    customer AS c
    INNER JOIN sales_fact_1997 AS s ON c.customer_id = s.customer_id
    INNER JOIN time_by_day AS t ON s.time_id = t.time_id
    INNER JOIN product AS p ON s.product_id = p.product_id
    INNER JOIN product_class AS pc ON p.product_class_id = pc.product_class_id
Where
    NOT t.the_day in ('Monday', 'Friday') OR
    (
        pc.product_family = 'Non-Consumable' AND
        NOT SUM(s.unit_sales) > 10 AND
        NOT (
            t.the_day in ('Monday', 'Friday') AND
            NOT (
                pc.product_family = 'Non-Consumable' OR
                (c.country = 'Canada' AND c.gender = 'F')
            )
        )
    )
GROUP BY concat(c.customer_id, s.time_id)

最终导致一个错误

#1111 - Invalid use of group function

但我不知道代码的哪一部分是错的。我很确定这可能是在哪里。但我不知道我做错了什么。

条件C是我真正挣扎的地方。我很好地处理了C的查询

SELECT
    t.time_id,
    c.customer_id,
    c.fullname,
    round(SUM(s.unit_sales),0) as tot
FROM
    customer as c
    INNER JOIN sales_fact_1997 as s ON c.customer_id = s.customer_id
    INNER JOIN time_by_day as t on s.time_id=t.time_id
GROUP BY concat(c.customer_id, s.time_id)
ORDER BY c.customer_id, t.time_id

但是，试图将它合并到主代码中对我来说很困难。

在线阅读时，我假设我应该使用“拥有”而不是“在哪里”。

如果有人能给我指明正确的方向，我会非常感激的。

这是我使用的数据库。

Answer 1

C:所有一次购买超过10件商品(sales_fact_1997.unit_sales)的消费者(sales_fact_1997.time_id)

您应该使用COUNT而不是SUM。

SELECT time_id,
       count(*) 
FROM sales_fact_1997  
GROUP BY time_id 
HAVING COUNT(*)>=10  ;

不需要count(*)，我只是为了显示结果

如果有帮助，你能试试吗：

SELECT c.lname,
       c.fname
FROM customer c
INNER JOIN 
( 
  SELECT time_id,customer_id,product_id 
  FROM sales_fact_1997  
  GROUP BY time_id,customer_id,product_id 
  HAVING COUNT(*)>=10 
) as s on c.customer_id=s.customer_id

INNER JOIN 
( 
  SELECT time_id,the_day 
  FROM time_by_day 
  WHERE the_day 
  NOT IN ('Monday','Friday')
) as t on s.time_id=t.time_id
INNER JOIN
(
SELECT product_family,product_id 
FROM product_class 
     INNER JOIN  product
on product_class.product_class_id=product.product_class_id 
WHERE product_family='Non-Consumable'
) pc on s.product_id=pc.product_id
where c.country='Canada' and c.gender ='F'  ;