I Can't Count!

whitebread

Hi Again,

I need help, in many ways but for this time we'll assume it's just for PHP coding. I would like to add a total of non-null fields from one field in my database on a PHP results page. Can someone help me?

The field (Named: Date_In) is a VARCHAR field but I can convert to a date type if need be. I only need to display the total amount of non-null records.

Thanks in advance.

kburger

SELECT COUNT(Date_In) FROM tablename WHERE Date_In != '';

or it could be
...WHERE Date_In NOT NULL;

Sxooter

Have you tried a simple test case:

test=# create table tt (v text);
CREATE TABLE
test=# insert into tt values (NULL);
INSERT 6262883 1
test=# insert into tt values (NULL);
INSERT 6262884 1
test=# insert into tt values ('bc');
INSERT 6262885 1
test=# insert into tt values ('de');
INSERT 6262886 1
test=# select count(v) from tt;

count

2
(1 row)

Works in PostgreSQL and MySQL.

Hint: NULLS don't count in aggregate functions.

Oh, and another hint for kburger, NULL <> ''

Just FYI.

kburger

Sorry, I should have written:

SELECT COUNT(Date_In) FROM tablename WHERE Date_In IS NOT NULL;

Sxooter

But you guys did get my point that nulls aren't counted in aggregates, right? So, things like

select avg(field) from table

mean that if there are four fields, and two are null, you'll get the average of the two non-null fields.

I think MySQL had a bug a LONG LONG time ago where they counted the null fields, but that was fixed eons ago.

kburger

whitebread, what exactly are you trying to do? Aren't you trying to count how many records have a non-null value for the Date_In field? If so, then my solution will give you exactly what you want.

Sxooter, the AVG(), SUM(), MIN(), MAX() or whatever other functions are irrelevant to whitebread's problem unless I'm misunderstanding what he's trying to do.

Sxooter

What, count(fieldname) or sum(fieldname) don't work for this problem? Or did I misunderstand the original question?

LordShryku

Sxooter is saying that the "IS NOT NULL" clause in the SQL is completely unnessecary because within an aggregate function, like count, it is implied already.
Here's an example I just ran on one of my tables:

SELECT count(time_closed)
  FROM requests
-------------------------
                    51267


SELECT count(time_closed)
  FROM requests
 WHERE time_closed IS NOT NULL
-------------------------
                    51267


SELECT count(*)
  FROM requests
-------------------------
                    51368

Sxooter

Was I really that unclear??? Honestly, I thought my very first post made the point pretty well.

LordShryku

Yes. No one understands you. 😃 :p

Sxooter

Now THAT I understand... haha 🙂

csn

What's the difference between count(field_name) and count(*)? count(field_name) doesn't include NULL's?

Sxooter

ding ding ding! You got it on the first try! 🙂

count(*) is a generic application of count. Otherwise you'd have to do count(coalesce(field,'SOME GARBAGE DATA') to make sure and count all the fields.

csn

Heh, scary I didn't know/remember that. Even mysql's FM explains it! Ah well, about the only time I use count() is count(primary_key) to find the number of rows, so count(*) would return the same number in that case. Since aggregates can have such lousy performance, I usually use triggers to update count, average, etc. fields.

Does PostgreSQL have a version of "select count(distinct field_name)"?

Sxooter

select count(distinct field_name) is standard SQL, and yes, PostgreSQL supports it.

whitebread

Ummm, Thanks guys. I got it working. However and here's the shot to you ego's, I wouldn't have gotten it working without you all.

Thanks Again.

Sxooter

Sorry about the posting "tennis match" there. Didn't mean to forget about the original poster in all that. 🙂

Glad to hear you got it working.