counting a non empty or null cell

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

counting a non empty or null cell

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: counting a non empty or null cell

Post 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
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

but if you do

Post 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!
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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)
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

well only this have helped :

Post by pelegk2 »

Code: Select all

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