MySQL error #1111 - Invalid use of group function

Question

Yes, this is an assignment. So the task was to output two columns of 'first name' and 'last name' with conditions:

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

A: All consumers that didn't shop on Monday and Friday (time_by_day.the_day)

B: All consumers who bought 'Non-Consumable' (product_class.product_family)

C: All consumers who bought more than 10 items (sales_fact_1997.unit_sales) at one time (sales_fact_1997.time_id)

D: Female consumers from Canada (consumer.gender, consumer.country)

This is what I got so far

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)

That ended up with an error

#1111 - Invalid use of group function

But I don't know which part of the code is wrong. I'm pretty sure that it's probably the WHERE part. But I don't know what I did wrong.

Condition C is where I'm really struggling. I manage just fine making a query of 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

But trying to incorporate it into the main code is hard for me.

Reading online I assume that I should probably use HAVING instead of WHERE.

I would really appreciate it if someone can point me in the right direction.

This is the database that I used.

Can you provide table description, some insert data and expected result ? `Where NOT t.the_day in` should be `where t.the_day not in` or you should use `not exists` — Ergest Basha, Sep 29 '21 at 08:21
@ErgestBasha [This](https://drive.google.com/file/d/1Kokrsc3HHEMng8FLAs6W_e4ulLh1CHf3/view?usp=sharing) is the database. — Skyblue, Sep 29 '21 at 08:31

score 0 · Answer 1 · answered Sep 29 '21 at 10:18

C: All consumers who bought more than 10 items (sales_fact_1997.unit_sales) at one time (sales_fact_1997.time_id)

You should use COUNT not SUM.

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

count(*) is not needed, I let just to show the results

Can you try if it helps:

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'  ;

MySQL error #1111 - Invalid use of group function

1 Answers1