Two queries; Counting fields and Adding dates.

[Fullest Empty]

Hi, I'm a complete loser novice, coming at you with a few loser novice queries.

I have a field, let's call it Field1, that holds duplicated data; what I would like to be able to do is have a query that counts how much of the entries in that field are the same and then declares that count. Does anyone know of a way to do this?

Also, I'm looking to define the data type of a field, lets call it Field2, as CurDate (so that whenever an entry is added to that table, Field2 automatically inserts today's date) - Anyone know how I would be able to do this? I would also like another field to be defined as (Field2 + 14 days). Is this possible?

I appreciate any feedback. Many thanks.

[Gorilla]

I assume you talk about fields in an SQL database. As my preferred database is PostgreSQL I have not tested the SQL statements below on any other database.

1) SELECT count(*), Field1 FROM table GROUP BY Field1. This will return a table showing the number of occurences of each value in Field1. By adding an ORDER BY clause you may have the rows sorted in various ways, for example by number of occurences of each value in decrasing order: SELECT count(*) AS count, Field1 FROM table GROUP BY Field1 ORDER BY count DESC.

2a) To create a table with a field that are automatically set when you create a record, do something like this: (PostgreSQL)

CREATE TABLE xx (
Field2 date DEFAULT now(),
Field2b timestamp DEFAULT now()
);

The second field will not only store the date, but the time of day.

2b) A field which is set to today's date + 14 days can be declared like this:

CREATE TABLE xx (
Field2c date DEFAULT now() + '14 days'::interval
);

If you want a field that is computed based on another field and automatically updated when the first field is set, you have to use a trigger.

[Fullest Empty]

Sweet, thanks. I'll try that out later on. Appreciate it.