MySQL divide result in several variables?

Question

I wanted to show for me the amount of orders of each buyer. And that works very well.

 select name, count(orderid) from buyer natural join reservation group by buyerid;

I get two results, one with 12 orders and the other one with 4 orders. Now I want to split this two outcomes at the quantity of their orders. For example all people who purchased more than 10 objects should be classified with an '+'. Those who bought < 10 objects should get a '-'. The difficulty is there are only single orders in the table reservations, which means: Person A ordered a Ship (Obj1), Person A ordered a DVD (Obj2) etc... The orders are single data sets in this table no sum or grouping (thats the reason for the select statement above).

However, I have another empty table to put the "name" and the "class" and it should be done by the using of a procedure.

Like this:

  create procedure dividing (in decisionpoint int)
  begin
  set @var = select count(orderid) from buyer....group by buyerid;
  -->Error because I get 2 Rows (Allowed is only one, but how can I solve this? I will continue with the logic)
  IF @var >= decisionpoint THEN do....
  ELSE do...
  end;

So it is actually simple but I don't see the solution at the moment... Maybe you are able to manage this.

Answer 1

you can turn your query into an inner query and do another outer SELECT to check for your condition

SELECT name,orders,
       CASE WHEN orders > 10 THEN "+"
            ELSE "-"
            END AS status
FROM
  (select name, count(orderid) as orders
    from buyer 
    natural join reservation 
    group by buyerid
  )AS T;

sqlfiddle

output:

name    orders  status
A       20        +
B        4        -