sqlself-joinmysql-5.0

Multiple results from single table in mysql


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.


Solution

  • 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