mysql query issue with calculated fields

Question

I want to select client transactions by means of generated by person.

It should show me all call back, appointment, sent email or any other status's count for particular agent (generated by id).

Schema:

CREATE TABLE IF NOT EXISTS `client_transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `generated_by_id` int(11) NOT NULL,
  `schedule_for_id` int(11) NOT NULL,
  `transaction_type` varchar(50) NOT NULL,
  `target_date_time` datetime NOT NULL,
  `date_added` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`)
)

Query:

SELECT generated_by_id as agent_id, count(*) as call_backs
FROM client_transaction
WHERE transaction_type='CALL_BACK'
GROUP BY generated_by_id

SQL FIDDLE

I have created query for one status. But, I need it for every status. And I also want to get agent name from other agent table. After getting all appropriate data, how can I left join with agent table?

Thanks.

please add the table structure for agent and with some data. — Abhik Chakraborty
– Abhik Chakraborty, Commented Mar 21, 2014 at 18:24
So the query you show works that way you want, but you want to understand how to group by transaction_type? — Mike Brant
– Mike Brant, Commented Mar 21, 2014 at 18:27

Mike Brant · Accepted Answer · 2014-03-21 18:46:28Z

1

Just add transaction_type as a field in your query, and apply another level of grouping on it:

SELECT
    generated_by_id AS agent_id,
    transaction_type,
    count(*) AS transaction_count
FROM client_transaction
GROUP BY generated_by_id, transaction_type
/* sort in some manner */
ORDER BY generated_by_id ASC, transaction_type ASC

An example of doing similar query with a join to an agent table might look like:

SELECT
    /* I am assuming field names on agent table
     * and am guessing you want to join to be able
     * to get something like agent name in this query
    */
    a.id AS `agent_id`,
    a.name AS `agent_name`,
    ct.transaction_type AS `transaction_type`,
    count(*) AS `transaction_count`
FROM agent AS a
LEFT JOIN client_transaction AS ct
    ON a.id = ct.generated_by_id
GROUP BY `agent_id`, `transaction_type`
/* sort in some manner */
ORDER BY `agent_id` ASC, `transaction_type` ASC

edited Mar 21, 2014 at 18:46

answered Mar 21, 2014 at 18:29

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sammitch · Accepted Answer · 2014-03-21 18:27:21Z

0

I need it for every status

Group by the transaction type.

SELECT generated_by_id as agent_id, transaction_type, count(*) as call_backs
FROM client_transaction
GROUP BY generated_by_id, transaction_type

how can I left join with agent table?

With a LEFT JOIN.

answered Mar 21, 2014 at 18:27

Sammitch

32.5k7 gold badges58 silver badges93 bronze badges

Collectives™ on Stack Overflow

mysql query issue with calculated fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related