I am developing a Diary Management Script using PHP and MySQL. I am stuck with a situation for which I am presenting below a hypothetical table structure and my requirement.
|---------------|--------------| | User Name | Status | |---------------|--------------| | John | Open | |---------------|--------------| | John | Assigned | |---------------|--------------| | John | Closed | |---------------|--------------| | Miller | Assigned | |---------------|--------------| | Miller | Assigned | |---------------|--------------| | Smith | Closed | |---------------|--------------|
I am expecting an ultimate webpage output like the below.
|---------------|--------------|--------------|--------------| | User Name | Open | Assigned | Closed | |---------------|--------------|--------------|--------------| | John | 1 | 1 | 1 | |---------------|--------------|--------------|--------------| | Miller | 0 | 2 | 0 | |---------------|--------------|--------------|--------------| | Smith | 0 | 0 | 1 | |---------------|--------------|--------------|--------------|
Please suggest if using MySQL query(using GROUP BY) itself can produce this ready made or I have to get the help of little bit of Programming logic. If so what should I code? I am very much helpless. I could provide the entire script here, but I did not do just to avoid confusion.
select `User Name`,
sum(case when Status = 'Open' then 1 end) as Open,
sum(case when Status = 'Assigned' then 1 end) as Assigned,
sum(case when Status = 'Closed' then 1 end) as Closed
from Status
group by `User Name`