how to transform results of syntax to become variable mysql

46 Views Asked by At

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.

1

There are 1 best solutions below

2
On BEST ANSWER

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