MySQL - Separating data within 1 column into 3 separate columns for a report

Question

We have a report to track how many edits our sales reps are doing. The current query to pull the number of edits on all 3 pages is below. We didn't care before which page they were making edits on, but now we want to see which pages they are make those edits on.

We are wanting to have 3 different columns: bhns, hns, chns, show up on the report and need to modify this query to show the different columns. So, split the 1 column (customer_edits) into 3 columns base on page.

SELECT
  count( `database2`.`sales_edits`.`id` ) AS `customer_edits`,
  `database2`.`sales_edits`.`rep` AS `rep`

FROM
  `database2`.`sales_edits` 
WHERE
  ((
      cast( `database2`.`sales_edits`.`date` AS date ) = curdate()) 
    AND ((
        `database2`.`sales_edits`.`page` = 'chs' 
        ) 
      OR ( `database2`.`sales_edits`.`page` = 'chns' ) 
    OR ( `database2`.`sales_edits`.`page` = 'bhns' ))) 
GROUP BY
  `database2`.`sales_edits`.`rep` 

sales_edit table:

Answer 1

It looks like you want conditional aggregation:

select
    rep,
    sum(page = 'chs')  customer_edits_chs,
    sum(page = 'chns') customer_edits_chns,
    sum(page = 'bhns') customer_edits_bhns
from database2.sales_edits 
where date = current_date and page in ('chs', 'chns', 'bhns')
group by rep

Your original code looks more complicated that it needs to:

• no need to prefix all columns with the schema and table name - a single table comes into play anyway (and if you had more than one, then you should use table aliases to shorten the code)

• the date casting seems unecessary; MySQL happily understands any string in 'yyyy-mm-dd' format as a date

• the repeated or conditions can be shortened with in

• it is probably unneeded to surround all identifiers with backticks, while they do not contain special characters