mysqlstored-proceduresmysql-workbenchmysql-variables

how to transform results of syntax to become variable mysql


i have a table with order_buyer_id as the id of transaction, createdby as the id of the buyer, createdAt as the date which transaction happen, and quantity as the weight of each transaction.

on my table i classified the buyer as 3 types :

- new buyer
- unique buyer
- existing buyer

this is the syntax to find out new buyer which i called A (new buyer) :

select 
       count(distinct om.createdby) as count_buyer
from (select count(xx.count_) as count_
          from (select count(createdby) as count_ from order_match
                where order_status_Id in (4, 5, 6, 8)
                 group by createdby
                 having count(createdby) = 1) xx
        ) x1,
        (select createdby
           from order_match
          group by createdby
          having count(createdby) = 1) yy,
        order_match om
 where yy.createdby = om.createdby and
 order_status_id in (4, 5, 6, 8)
 and om.createdAt >= paramdatefrom
   and om.createdAt <= paramdateto
   and NOT EXISTS (select 1 from order_match om2
                where om.createdby = om2.createdby
               and order_status_id in (4, 5, 6, 8)
                  and om2.createdAt < paramdatefrom);

this is the syntax to find out repeat buyer, called B (unique buyer) :

    select
           count(distinct om.createdby) as count
   from (select count(xx.count_) as count_
          from (select count(createdby) as count_ from order_match
                where order_status_Id in (4, 5, 6, 8)
                 group by createdby
                 ) xx
        ) x1,
        (select createdby
           from order_match
          group by createdby
          ) yy,
        order_match om
 where yy.createdby = om.createdby and
 order_status_id in (4, 5, 6, 8)
 and om.createdAt >= paramdatefrom
   and om.createdAt <= paramdateto;

;

and this is the syntax to find out existing buyer, called C (existing buyer):

select
  count(distinct om.createdby) as count
from
  order_match om
  where om.order_status_id in (4,5,6,8)
  and om.createdAt <= paramdateto
  and om.createdAt >= paramdatefrom
  and EXISTS (select 1 from order_match om2
  where om.createdby = om2.createdby
  and om2.createdAt < paramdatefrom and
  om2.order_status_id in (4, 5, 6, 8)) 
  ;

basically i want all of this syntax to become variable A, B, C so i can count the precentage for my needs, based on my explanation, expected results just like this

select (A (the result of syntax new Buyer) : B (the result of syntax unique buyer)) * 100 as percentage_1

and select (100 - percentage_1) as percentage_2

the point is how to make every result of syntax to become variable so i can count percentage_1 and percentage_2 just like expected results.


Solution

  • To test bigger querys you must provide some data, to test the query properly see

    And i couldn't find in your description, why you needed result_c, but you can now use it.

    by the way this are algorithms or Querys and not syntax..

    SELECT 
        result_a / result_b * 100 AS percentage_1,
        100 - (result_a / result_b * 100) AS percentage_2
    FROM
        (SELECT 
            (SELECT 
                        COUNT(DISTINCT om.createdby) AS count_buyer
                    FROM
                        (SELECT 
                        COUNT(xx.count_) AS count_
                    FROM
                        (SELECT 
                        COUNT(createdby) AS count_
                    FROM
                        order_match
                    WHERE
                        order_status_Id IN (4 , 5, 6, 8)
                    GROUP BY createdby
                    HAVING COUNT(createdby) = 1) xx) x1, (SELECT 
                        createdby
                    FROM
                        order_match
                    GROUP BY createdby
                    HAVING COUNT(createdby) = 1) yy, order_match om
                    WHERE
                        yy.createdby = om.createdby
                            AND order_status_id IN (4 , 5, 6, 8)
                            AND om.createdAt >= paramdatefrom
                            AND om.createdAt <= paramdateto
                            AND NOT EXISTS( SELECT 
                                1
                            FROM
                                order_match om2
                            WHERE
                                om.createdby = om2.createdby
                                    AND order_status_id IN (4 , 5, 6, 8)
                                    AND om2.createdAt < paramdatefrom)) result_a,
                (SELECT 
                        COUNT(DISTINCT om.createdby) AS count
                    FROM
                        (SELECT 
                        COUNT(xx.count_) AS count_
                    FROM
                        (SELECT 
                        COUNT(createdby) AS count_
                    FROM
                        order_match
                    WHERE
                        order_status_Id IN (4 , 5, 6, 8)
                    GROUP BY createdby) xx) x1, (SELECT 
                        createdby
                    FROM
                        order_match
                    GROUP BY createdby) yy, order_match om
                    WHERE
                        yy.createdby = om.createdby
                            AND order_status_id IN (4 , 5, 6, 8)
                            AND om.createdAt >= paramdatefrom
                            AND om.createdAt <= paramdateto) result_b,
                (SELECT 
                        COUNT(DISTINCT om.createdby) AS count
                    FROM
                        order_match om
                    WHERE
                        om.order_status_id IN (4 , 5, 6, 8)
                            AND om.createdAt <= paramdateto
                            AND om.createdAt >= paramdatefrom
                            AND EXISTS( SELECT 
                                1
                            FROM
                                order_match om2
                            WHERE
                                om.createdby = om2.createdby
                                    AND om2.createdAt < paramdatefrom
                                    AND om2.order_status_id IN (4 , 5, 6, 8))) result_c
        ) a