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.
The primary key is on id an I use the id for safety, so I will not delete or update something wrong.
I am using mysql 4.0.24 since the 4.1.x is maked in the portage of gentoo, they cosider it is unstable and I dont want to assume any kind of risk.


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.

SELECT user_id,ev_status,COUNT(*) from events WHERE user_id='abc' and ev_status='1' GROUP BY user_id;
+---------+-----------+----------+
| user_id | ev_status | COUNT(*) |
+---------+-----------+----------+
| abc | 1 | 75 |
+---------+-----------+----------+
1 row in set (0.01 sec)


SELECT user_id,ev_status,COUNT(*) from events WHERE user_id='test' and ev_status='1' GROUP BY user_id;
Empty set (0.01 sec)


So what I need is to show for all the user_id even if the query returns Empty set if I run something like:
SELECT user_id,ev_status,COUNT(*) from events WHERE ev_status='1' GROUP BY user_id;



Best regards, Cristi


Rhino wrote:

----- Original Message ----- From: "Michael J. Pawlowsky" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Wednesday, April 06, 2005 7:51 PM
Subject: Re: I need some help





Rhino wrote:



----- Original Message ----- From: "iNFERNo" <[EMAIL PROTECTED]>
To: "mysql" <mysql@lists.mysql.com>
Sent: Wednesday, April 06, 2005 5:23 PM
Subject: I need some help







Hi,

          I have a problem: I am using mysql 4.0.24 and I need to make
some reports from a database:

mysql> describe events;



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


| Field | Type | Null | Key | Default | Extra


|


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


| id | int(10) unsigned | | PRI | NULL | auto_increment


|


| user_id | varchar(55) | | | 0 |


|


| dep_id | int(15) | | | 0 |


|


| event_id | int(15) | | | 0 |


|


| year | int(15) | | | 0 |


|


| day | int(15) | | | 0 |


|


| month | int(15) | | | 0 |


|


| ev_status | int(11) | YES | | 0 |


|


| ev_type | int(11) | YES | | 0 |


|


| ev_priority | int(11) | YES | | 0 |


|


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


I need something to get:

| USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 |
EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 |

user_1         1            COUNT                 COUNT
COUNT                 COUNT               COUNT
COUNT               COUNT

user_2         1            COUNT                 COUNT
COUNT                 COUNT               COUNT
COUNT               COUNT

user_3         2            COUNT                 COUNT
          COUNT                 COUNT
COUNT                 COUNT               COUNT

.
.
.


The problem is that the way I am getting this now is with a query for each user and for each ev_status and the output is in PHP. Is there a better way to do this ? I am only a beginner in MySQL and want to learn more and improve.





First of all, congratulations on identifying your version of MySQL and
giving the definition of your table. That is an excellent start to


getting


an answer to your question. Far too many people post here without
identifying their MySQL version or giving the definition of their tables,
making it very difficult to answer their questions without having to ask
many followup questions.





Am I missing something...   but isn't  id defined as the primary key?




You are absolutely right; I'm sorry, I missed the primary key designation. (I was expecting the primary key definition after the last column definition which, now that I think about it, was just plain wrong!)



On a different topic..  if you want to make your life easy with PHP.
Instead of saving, the year, month, day...  personally I always simply
use an int and save all dates as Unix Timestamps. As long as you are
working more or less in this century, you will be fine.
That is a personal  choice, from someone that has built MANY calendars.

As for selecting all of them like that. I question why..  but....

SELECT user_id,  dep_id, IF(ev_status=1,1,0), IF(ev_status=2,10) ...
and so on.

But I really don't get why you would be doing that



I'm afraid I'm a bit confused too: if there really is just one row per ID,
what would that row look like? There would have to be a single value for
ev_status so wouldn't you just report that value? What is there to count? A
small bit of sample data would make it easier to visualize what you want and
why you want it.

Rhino








Reply via email to