Aggregate MySQL Queries

Question

Aggregate below queries

1.

SELECT business_id_fk,business_name, 
              IFNULL(count(orderid_customer),0) AS total,
              IFNULL(sum(CASE WHEN order_status IN ('CRD') THEN 1 ELSE 0 
              END), 0) AS crude,
              IFNULL(sum(CASE WHEN order_status IN ('UNA') THEN 1 ELSE 0 
              END), 0) AS unassigned,
              IFNULL(sum(CASE WHEN order_status IN ('DEL' , 'MCP' , 'CMP') 
              THEN 1 ELSE 0 END), 0) AS delivered,
              IFNULL(sum(CASE WHEN order_status IN ('CNL') THEN 1 ELSE 0 
              END), 0) AS cancelled,
              IFNULL(sum(CASE WHEN order_status IN ( 'CAP' , 'CAD' , 'RSP' , 
              'RSD') THEN 1 ELSE 0 END), 0) AS postponed,
              IFNULL(SUM(total), 0) AS 'Estimated_Revenue',
              IFNULL(SUM(CASE WHEN order_status IN ('DEL' , 'MCP', 'CMP') 
              THEN total ELSE 0 END), 0) AS 'Generated_Revenue'
              FROM view_orders where order_datetime between '2017-08-01 
              00:00:00' and '2017-10-17 23:59:59' and parent_id IS NULL
              GROUP BY business_id_fk ORDER BY total DESC;

2.

SELECT A.business_id_fk,A.business_name,ifnull(((A.TotalPrice / 
A.TotalQuantity)*100),0) AS 'sla_adherence' FROM (SELECT 
business_id_fk,business_name,
sum(sla_del_datetime > delivery_datetime) AS TotalPrice
,sum(order_status in('DEL','MCP','CMP')) AS TotalQuantity
FROM view_orders where parent_id is null and order_datetime between '2017-
08-01 00:00:00' and '2017-10-17 23:59:59'
GROUP BY business_id_fk) AS A;

3.

SELECT A.business_id_fk,A.business_name,ifnull(((A.Totaldelivered / 
A.Totalorders)*100),0) AS 'strike_rate' FROM (SELECT 
business_id_fk,business_name,
sum(order_status in('DEL','MCP','CMP')) AS Totaldelivered
, count(*) AS Totalorders
FROM view_orders where parent_id is null and order_datetime between '2017-
08-01 00:00:00' and '2017-10-17 23:59:59'
GROUP BY business_id_fk) AS A;

To get the result like

Business_id_fk BusinessName Total Delivered Unassigned Cancelled Postponed EstimatedRevenue GeneratedRevenue SLAadherance Strikerate  
             2 DEF             10         5          2         1         0              200              100          100         50  
             1 ABC             20        10          4         2         0              200              100          100         50  
          Null Null            10        10          0         0         0              100              100          100        100  

Here business name having null is Individual customer Orders(non-business orders).
Thank you in advance :-)

Answer 1

Like this:

SELECT
  business_id_fk,business_name,
  total,
  crude,
  unassigned,
  delivered,
  cancelled,
  postponed,
  Estimated_Revenue,
  Generated_Revenue,
  TotalPrice,
  Totaldelivered,  
  Totalorders,
  ifnull(((TotalPrice / TotalQuantity)*100),0) AS 'sla_adherence',
  ifnull(((Totaldelivered / Totalorders)*100),0) AS 'strike_rate' 
FROM
(

    SELECT
      business_id_fk,business_name, 
      IFNULL(count(orderid_customer),0) AS total,
      IFNULL(sum(CASE WHEN order_status IN ('CRD') THEN 1 ELSE 0 END), 0) AS crude,
      IFNULL(sum(CASE WHEN order_status IN ('UNA') THEN 1 ELSE 0 END), 0) AS unassigned,
      IFNULL(sum(CASE WHEN order_status IN ('DEL' , 'MCP' , 'CMP') THEN 1 ELSE 0 END), 0) AS delivered,
      IFNULL(sum(CASE WHEN order_status IN ('CNL') THEN 1 ELSE 0 END), 0) AS cancelled,
      IFNULL(sum(CASE WHEN order_status IN ( 'CAP' , 'CAD' , 'RSP' , 'RSD') THEN 1 ELSE 0 END), 0) AS postponed,
      IFNULL(SUM(total), 0) AS 'Estimated_Revenue',
      IFNULL(SUM(CASE WHEN order_status IN ('DEL' , 'MCP', 'CMP') THEN total ELSE 0 END), 0) AS 'Generated_Revenue',
      sum(sla_del_datetime > delivery_datetime) AS TotalPrice, 
      sum(order_status in('DEL','MCP','CMP')) AS TotalQuantity,
      sum(order_status in('DEL','MCP','CMP')) AS Totaldelivered, 
      count(*) AS Totalorders
    FROM view_orders 
    where order_datetime between '2017-08-01 00:00:00' and '2017-10-17 23:59:59' 
      and parent_id IS NULL
    GROUP BY business_id_fk


) AS A
ORDER BY total DESC;