sql for displaying query field wise

Question

I have written an sql to get ticket information per user on a database.

select u.realname,count(t.id) as ct,t.Status
from Tickets t,Users u
where u.id=t.Owner and (t.status='New' or t.status='Stalled' or t.status='Open')
group by u.realname,t.status;

it gives output like this

| Enoch Root           |     1 | new     |
| Enoch Root           |     1 | stalled |
| xyz   Chan           |     1 | open    |
| Jim                  |    22 | stalled |

but what i am looking for is users against status

like

       open   stalled  new
 root    1       10      14

Please let me know

score 0 · Answer 1 · answered Dec 20 '13 at 16:00

SELECT
  u.realname,
  SUM(t.status='Open') AS `Open`,
  SUM(t.status='Stalled') AS `Stalled`,
  SUM(t.status='New') AS `New`
FROM Tickets t INNER JOIN Users u
     ON u.id=t.Owner
WHERE (t.status='New' OR t.status='Stalled' OR t.status='Open')
GROUP BY
  u.id, u.realname

score 0 · Accepted Answer · answered Dec 20 '13 at 16:02

select u.realname, 
  sum( if( t.status = 'New', 1, 0 ) ) as new, 
  sum( if( t.status = 'Stalled', 1, 0 ) ) as stalled, 
  sum( if( t.status = 'Open', 1, 0 ) ) as open
from Tickets t, Users u
where 
  u.id = t.Owner and 
  t.status in ( 'New', 'Stalled', 'Open' )
group by u.realname

sql for displaying query field wise

2 Answers2