I have a table in my database with columns:
Lead_id INT(11) PRIMARY KEY NOT NULL
user_id INT(11)
lead_status ENUM('active','win','loss')
below sample data:
Lead_id user_id lead_status
1 45 active
2 31 win
3 11 loss
4 45 win
5 11 active
6 45 loss
I need to query the table above to get the following output:
user_id total_leads active win loss
45 3 1 1 1
31 1 0 1 0
11 2 1 0 1
I intend to graphically represent the data in the output table. Any help would be appreciated.
Try this:
SELECT
user_id,
COUNT(*) AS total_leads,
SUM(lead_status = 'active') AS active,
SUM(lead_status = 'win') AS win,
SUM(lead_status = 'loss') AS loss
FROM your_table
GROUP BY user_id