2

enter image description here

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.

enter image description here

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

2 Answers 2

4

Just use a common table expression:

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

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
3
  • 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?
    – bagMan
    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.
    – Vérace
    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?
    – bagMan
    Dec 4, 2016 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.