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;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
