0

Currently I'm using CodeIgniter and MySQL to retrieve a count of data within a particular timeframe. My table data looks something like this:

enter image description here

Where there are different agents who are changing statuses in a particular timeframe. So what I want is to layout all the agents in the table and display what status change they had made in a particular timeframe. To get a count of all the data in a particular timeframe I used this following query which works:

SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted, SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold, SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let FROM `crm_logs` WHERE cast(logtime as date) BETWEEN "2021-09-25" AND "2021-10-01"

But now I want it so that it is grouped by the agent_is, and the respective agent_id has the status next to him.

2
  • 1
    Does adding GROUP BY agent_id not work in your query? Do you get an error message? Can you provide an example of what the output should look like?
    – dwhitemv
    Oct 11 at 19:25
  • Why is logtime being cast to date? Note SUM(CASE WHEN status_to = 'Draft' THEN 1 END) is equivalent to SUM(status_to = 'Draft').
    – danblack
    Oct 12 at 0:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.