Using timestamp as primary key? - Rajeev Rumale - com.mysql.lists.perl

9 messages in com.mysql.lists.perlUsing timestamp as primary key?

From	Sent On	Attachments
Jeremy E Cath	26 Aug 2001 20:55
Simon Oliver	27 Aug 2001 02:10
Rajeev Rumale	29 Aug 2001 20:50
Steve Howard	01 Sep 2001 07:57
Robert Goff	01 Sep 2001 08:18
Rajeev Rumale	02 Sep 2001 19:17
Rajeev Rumale	02 Sep 2001 19:21
Steve Howard	02 Sep 2001 21:10
wshe...@lexmark.com	04 Sep 2001 06:24

Subject:	Using timestamp as primary key?
From:	Rajeev Rumale (raj...@myangel.com)
Date:	09/02/2001 07:21:05 PM
List:	com.mysql.lists.perl

Hi,

Thank you all for anwering me preious question. I am hope ful that I will get answer for my next one also.

I want to know if it is safe enough to use a field with "timestamp" type as primary key for a table. Will it cause any problem while processing muiltple requests?

with regards

Rajeev

-------------------------------------------------------------------- Happiness, Happiness, The greatest gift that I possess, I thank the Lord that I've been blessed, With more than my share of happiness.

--------------------------------------------------------------------

Unless I'm missing something, you cannot do this all with one query. You can do it with two queries, but I'm not sure that would be any more efficient than handling each with a separate query. The reason you can't get it all in one query is that one of these will require a "Group by" clause, and since you are wanting results that have nothing to do with either the aggregate or the group by, trying to combine that will throw an error. This is untested since I didn't actually have your table to work with, but I tested the concept to be sure I was right on that before I typed this in:

Query 1 (Gives total number of employees, and those present, and those absent):

SELECT COUNT(*) as TOTAL, (SELECT COUNT(*) FROM Table WHERE status = 'P') as PRESENT, (SELECT COUNT(*) FROM Table WHERE STATUS IN ('L', 'A', 'O')) AS ABSENT FROM Table

Query 2 (Gives the number of employees of each type):

SELECT type, COUNT(*) as NUMBER FROM Table GROUP BY type

Now, just embed those into your Perl and you can get the results you are looking for into a report.

Hope this helps,

Steve H.

-----Original Message----- From: Rajeev Rumale [mailto:raj...@myangel.com] Sent: Wednesday, August 29, 2001 10:51 PM To: dbi-...@perl.org; msql...@lists.mysql.com Subject: counting no. of records matching condition.

Greeting every one,

I need to know the best way to count the number of occurances of multiple fields in a single table matching some conditions.

For example I have a table consisting of fields id, name, type, status,

Here the type field can have values W - Worker, S - supervisior M - Manager A - Adminstrative staff T - Tempory / Contract worker

And the status field can have values P - present on duty L - On Leave A - Absent (without applying leave ) O - Off duty (long vacation given for perticular positions as per company terms)

Here I need to calculate 1. Total no of emplaoyees, 2. No employees of each type, 3. No. of employees Present on duty, 4. No. of employees Absent.

Can we do this with a single sql statement. Currently I am using one for each of the condition, which definately not a good way. The other way I can look for is to fetch all records and then do calcuations in the Perl Script.

I am use MySql, with Active Perl on a win2k and IIS.

Kindly suggest.

Regards

Rajeev Rumale

--------------------------------------------------------------------------

--------------------- Your diamonds are not in far distant mountains or in yonder seas; they are in your own backyard, if you but dig for them.

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail msql...@lists.mysql.com instead.