Page 1 of 1

counting a non empty or null cell

Posted: Tue Apr 12, 2005 9:20 am
by pelegk2
i have this query :

Code: Select all

SELECT sum(weight)as weight,
             Count(TEUDAT_MISH) as total_TEUDAT_MISH 
             from order_header 
            where del_id=4018


i want to sum all the weight - and that woks find!
the point is that i want to count TEUDAT_MISH - but the thing is that in some of the rows there maybe not a TEUDAT_MISH,
whcih mean the cells hold NULL or 0 !!!!
(if there is a TEUDAT_MISH then TEUDAT_MISH is a string of at least 6 chars!!!)

what do i nee to change in the select so it wil lwork fine!
thnaks i nadvance
peleg

Re: counting a non empty or null cell

Posted: Tue Apr 12, 2005 9:25 am
by Chris Corbyn
Not 100% what you mean. Are you saying you don't want the NULL values in the COUNT?

I guess not cos I see how good you are with your SQL in the past so I'm not trying to be patronising but just in case.... :?

Code: Select all

SELECT sum(weight)as weight,
             Count(TEUDAT_MISH) as total_TEUDAT_MISH 
             from order_header 
            where del_id=4018 AND TEUDAT_MISH NOT NULL

but if you do

Posted: Tue Apr 12, 2005 9:28 am
by pelegk2

Code: Select all

AND TEUDAT_MISH NOT NULL
it won't count the rows that dont have TEUDAT_MISH
but do have weight!

Posted: Tue Apr 12, 2005 9:29 am
by CoderGoblin

Code: Select all

SELECT sum(weight)as weight,
             Count(TEUDAT_MISH) as total_TEUDAT_MISH 
FROM order_header 
WHERE del_id=4018 AND
      TEUDAT_MISH IS NOT NULL AND
      TEUDAT_MISH<>'0'
GROUP BY TEUDAT_MISH
ORDER BY TEUDAT_MISH
Assuming TEUDAT_MISH is a varchar... should work as I understand the question (not sure what TEUDAT_MISH means but assume you want it ordered).

It is always recommended to default database tables. If you default TEUDAT_MISH to '' and never update it to '0' then only one check is ever required (TEUDAT <> '').

EDIT:: Requirements not as first understood.. (In Postgres you could use the coalesce function not sure on mysql)

well only this have helped :

Posted: Tue Apr 12, 2005 9:36 am
by pelegk2

Code: Select all

AND (TEUDAT_MISH!=null or TEUDAT_MISH!=0)
TEUDAT_MISH is varchar(1)