This is the outcome I'm trying to get from a select query, This is to calculate employee stats, in the last column 'sales' how can i pass the data of every employee work in that business and total revenue generated daily, i want to know how can i show 'total_sales' data like in the shown table.

i've used the JSON_OBJECTAGG like this -

JSON_ARRAYAGG(JSON_OBJECT(emp.name , ad.price)) AS total_sales

but it returns only 1st price of that day, I need to -

JSON_ARRAYAGG(JSON_OBJECT(emp.name , SUM(ad.price))) AS total_sales

but its not correct and giving me errors, how can i solve this , also if i can use any other methods please let me know.

SET @formDate=DATE_SUB(now(),INTERVAL 30 DAY);

SELECT 
      DATE(appoint.booking_date) AS 'date',
      DATE_FORMAT(appoint.booking_date,'%M') AS 'month',
      (SELECT COUNT(DISTINCT emp.id) FROM employee emp WHERE emp.entity_id='126' AND emp.status=1) AS 'total_employee',
      IFNULL(SUM(ad.price),0) AS total_revenue,
      JSON_ARRAYAGG(JSON_OBJECT(emp.name , ad.price)) AS total_sales
      FROM employee emp 
      
      LEFT JOIN entity ent ON emp.entity_id=ent.id
      LEFT JOIN appointment_status appstat ON emp.entity_id=appstat.entity_id
      LEFT JOIN appointments appoint ON appstat.appointment_id=appoint.id
      LEFT JOIN appointment_details ad ON appoint.id=ad.appointment_id

      WHERE 
          emp.status=1 AND
          emp.entity_id='126' AND
          appstat.current_status='4' AND
          appstat.assign_to = emp.account_id AND
          DATE_FORMAT(appoint.booking_date,'%Y-%m-%d')
                    BETWEEN DATE_FORMAT(@formDate,'%Y-%m-%d') AND 
                            DATE_FORMAT(now(),'%Y-%m-%d') 
      
      GROUP BY
             appoint.booking_date DESC

Thanks.

CodePudding user response：

I have this link [1]: https://www.sitepoint.com/use-json-data-fields-mysql-databases/ I hope it can help you

CodePudding user response：

Test this:

SELECT `date`,
       `month`,
       total_employee,
       total_revenue,
       JSON_ARRAYAGG(JSON_OBJECT(name, total_price)) AS total_sales
FROM

-- your query, group by date and name, obtain SUM(prices)
  (
    SELECT DATE(appoint.booking_date) AS `date`,
           DATE_FORMAT(appoint.booking_date, '%M') AS `month`,
           ( SELECT COUNT(DISTINCT emp.id)
             FROM employee emp
             WHERE emp.entity_id='126'
             AND emp.status=1
             ) AS total_employee,
           IFNULL(SUM(ad.price), 0) AS total_revenue,
           emp.name,
           SUM(ad.price) AS total_price
   FROM employee emp
   LEFT JOIN entity ent ON emp.entity_id=ent.id
   LEFT JOIN appointment_status appstat ON emp.entity_id=appstat.entity_id
   LEFT JOIN appointments appoint ON appstat.appointment_id=appoint.id
   LEFT JOIN appointment_details ad ON appoint.id=ad.appointment_id
   WHERE emp.status=1
     AND emp.entity_id='126'
     AND appstat.current_status='4'
     AND appstat.assign_to = emp.account_id
     AND DATE(appoint.booking_date) 
         BETWEEN CURRENT_DATE - INTERVAL 30 DAY 
             AND CURRENT_DATE
   GROUP BY `date`,
            emp.name) AS sum_prices

GROUP BY 1,2,3,4