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 |
getting+-------------+------------------+------+-----+---------+----------------+
First of all, congratulations on identifying your version of MySQL andI 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.
giving the definition of your table. That is an excellent start to
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
