# Problem with compare value with its aggregate function within the same table

I need to find the list of `customer_id` for customer who have spent more than the average customer.

This is the the query I use to generate table above:

``````SELECT customer_id, sum(Total) AS customer_total
FROM (SELECT T.order_id, customer_id, Total
FROM (SELECT order_id, SUM(quantity * B.price) AS Total
FROM ORDERITEM AS O, BOOK AS B
WHERE B.b_id = O.bid
GROUP BY O.order_id) AS A JOIN "TRANSACTION" AS T
ON A.order_id = T.order_id)
GROUP BY customer_id
ORDER BY customer_total DESC
``````

How can I start from here to find the list of customers who have spent more than the average customer? And also how to give the limit of digits for double? Thanks in advance!

Below is the query I tried, but it returns empty. And I am sure the correct result is not empty.

• I assume the "average customer" is the customer who has MEDIAN total cost. How to find median? Dec 4, 2016 at 22:17

Just use a common table expression:

``````WITH CustTotals AS ( ... your query above ...)
SELECT *
FROM CustTotals
WHERE customer_total > (SELECT AVG(customer_total)
FROM CustTotals);
``````
• OMG, that was brilliant ! It works perfect, thank you so much! Dec 5, 2016 at 15:47
• Nice! +1 - I forgot that SQLite has CTEs. Dec 6, 2016 at 2:50

Try something like this (note: untested).

``````SELECT customer_id, cust_tot, cust_avg FROM
(
SELECT customer_id, ROUND(customer_total, 2) AS cust_tot, AVG(customer_total) AS cust_avg
FROM
(
-- This is your query yq
SELECT customer_id, sum(Total) AS customer_total
FROM
(
SELECT T.order_id, customer_id, Total
FROM
(
SELECT order_id, SUM(quantity * B.price) AS Total
FROM ORDERITEM AS O, BOOK AS B
WHERE B.b_id = O.bid
GROUP BY O.order_id) AS A
JOIN
ORDER_TRANSACTION AS T
ON A.order_id = T.order_id
)
GROUP BY customer_id
ORDER BY customer_total DESC  -- yq ends
) AS TAB
) AS TAB2
WHERE TAB2.cust_tot > TAB2.cust_avg
``````
• I am kind of newbie with database, I didn't realize that "TRANSACTION" is a keyword. The compiler return an error says misuse of aggregate function AVG() , I suppose that aggregate function only allowed in SELECT? Dec 4, 2016 at 22:30
• I have edited my answer. That'll teach me to answer here when it's late and I've no machine for testing! Again: not tested. Dec 4, 2016 at 22:46
• Just now I try the same thing, to get total spend and average spend at same time, then compare it, but the result is empty. It seems it cannot compare the same attribute within the same table? Dec 4, 2016 at 22:56