Re: I need some help

[iNFERNo]

Hi,

   Thank you very much that was what I looking for.

Best regards,
Cristi

Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,
iNFERNo <[EMAIL PROTECTED]> writes:

Hi,
First of all thank you all for the quick replys.

Now here's:

select * from events limit 10;

+--+------ --+--------+------------+------+-----+-------+----------+-----------+-------------- +

| id | user_id | dep_id | event_id | year | day | month | ev_status |
ev_type | ev_priority |

| 12 | abc | 0 | 123832 | 2005 | 21 | 3 |
2 | 344 | 30 |

| 13 | abc | 0 | 123255 | 2005 | 21 | 3 |
6 | 345 | 10 |

| 14 | abc | 0 | 123832 | 2005 | 21 | 3 |
1 | 345 | 30 |

| 15 | abc | 0 | 123994 | 2005 | 21 | 3 |
1 | 344 | 30 |

| 16 | abc | 0 | 123994 | 2005 | 21 | 3 |
2 | 344 | 30 |

| 17 | abc | 0 | 123994 | 2005 | 21 | 3 |
6 | 344 | 30 |

| 18 | abc | 0 | 123832 | 2005 | 21 | 3 |
6 | 344 | 30 |

| 19 | abc | 0 | 123186 | 2005 | 21 | 3 |
1 | 344 | 30 |

| 20 | abc | 0 | 123186 | 2005 | 21 | 3 |
2 | 344 | 30 |

| 21 | abc | 0 | 123990 | 2005 | 22 | 3 |
6 | 337 | 10 |

+--+------ --+--------+------------+------+-----+-------+----------+-----------+---------------+

This is how the data is in the table.

Why do you store year, day and month in separate columns instead of
using a DATE column?

I need to get a report out of the DB that will show me for each
user_id how many events(count) are closed with the ev_status=0,
ev_status=1 ..., ev_status=6. Like I've said before the way I am
getting the date now I dont think is the best and I would like some
help so I can improve my skills.

P.S.:A related problem is that for a user_id that has none events
closed with ev_status=1 it's not listed in the query.

Try something like

SELECT user_id,
      sum(CASE ev_status WHEN 0 THEN 1 ELSE 0 END),
      sum(CASE ev_status WHEN 1 THEN 1 ELSE 0 END),
      sum(CASE ev_status WHEN 2 THEN 1 ELSE 0 END),
      sum(CASE ev_status WHEN 3 THEN 1 ELSE 0 END),
      sum(CASE ev_status WHEN 4 THEN 1 ELSE 0 END),
      sum(CASE ev_status WHEN 5 THEN 1 ELSE 0 END),
      sum(CASE ev_status WHEN 6 THEN 1 ELSE 0 END)
FROM events
GROUP BY user_id;