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.
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