Geek asks and answers

Using the Where clause in a join using Group by and Order By

I am using MySQL classicmodels database. The following query works fine (check out the where clause)

select 
    customers.customerNumber as 'Customer ID',
    customers.customerName as 'Customer Name',
    count(orders.orderNumber) as 'Total Orders Placed'
from customers
left join orders on customers.customerNumber = orders.customerNumber
where customers.customerNumber > 200
group by
    customers.customerNumber
order by 
    3 asc

But the following errors lead to an error. The goal is to display only those customers in the resulting rowset that have placed more than three orders. What am I doing wrong?

select 
    customers.customerNumber as 'Customer ID',
    customers.customerName as 'Customer Name',
    count(orders.orderNumber) as 'Total Orders Placed'
from customers
left join orders on customers.customerNumber = orders.customerNumber
where count(orders.orderNumber) > 3
group by
    customers.customerNumber
order by 
    3 asc

MySQL error: Error code: 1111. Invalid use of group function

+3
source share
4 answers

Aggregate functions ( COUNT(), AVG(), SUM(),etc.) cannot appear in a sentence WHEREbecause of when they are evaluated. Instead, they belong to a sentence HAVING:

select 
    customers.customerNumber as 'Customer ID',
    customers.customerName as 'Customer Name',
    count(orders.orderNumber) as 'Total Orders Placed'
from customers
left join orders on customers.customerNumber = orders.customerNumber
group by
    customers.customerNumber
HAVING count(orders.orderNumber) > 3
order by 
    3 asc
+8

.

SELECT 
  c.customerNumber AS 'Customer ID',
  c.customerName AS 'Customer Name',
  o.orderCount AS 'Total Orders Placed'
FROM 
  customers c
LEFT JOIN
  (SELECT
     customerNumber, 
     COUNT(*) AS orderCount
   FROM
     orders
   GROUP BY
     customerNumber) o
 ON
   c.customerNumber = o.customerNumber
 WHERE
   o.orderCount > 3
 ORDER BY
   3
+1

where where

You can rewrite the request as


select customers.customerNumber as 'Customer ID',
       customers.customerName as 'Customer Name',
       count(orders.orderNumber) as 'Total Orders Placed'
  from customers left join orders on customers.customerNumber =
                                     orders.customerNumber
 where
 (SELECT CASE
        WHEN count(orders.orderNumber) > 3 THEN
             'TRUE'
        ELSE
             'FALSE'
        END
   FROM DUAL) = 'TRUE'
 group by customers.customerNumber
 order by 3 asc

Here, the count function is used in the select clause, which returns TRUE FALSE strings according to the calculation of the count.

Hope this helps

+1
source

Please, try

select 
  customers.customerNumber as 'Customer ID',
  customers.customerName as 'Customer Name',
  count(orders.orderNumber) as 'Total Orders Placed'
from customers
inner join orders on customers.customerNumber = orders.customerNumber
group by
  customers.customerNumber, 
  customers.customerName
having count(orders.orderNumber) > 3
order by 
  3 asc
0
source

More articles:


All Articles