473,597 Members | 2,654 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Count of NULL and NOT NULLS

Jim
How would I write a query to count the number of instances where a the
PREP field is NULL and when it is not null. I know how to do it
seperately, can I get the numbers in the same query. I want something
like this

Project #PREP_NOTNULL #PREP_NULL

Thanks,

Jim

Dec 15 '06 #1
3 7577
que
select concat('isnull( prep) = ' ,isnull(prep)) `condition`, count(*)
`count` from your_table group by isnull(prep)

Jim íàïèñàâ:
How would I write a query to count the number of instances where a the
PREP field is NULL and when it is not null. I know how to do it
seperately, can I get the numbers in the same query. I want something
like this

Project #PREP_NOTNULL #PREP_NULL

Thanks,

Jim
Dec 16 '06 #2
Jim
Thanks,

Is there a way to get the counts in the same ROW

que wrote:
select concat('isnull( prep) = ' ,isnull(prep)) `condition`, count(*)
`count` from your_table group by isnull(prep)

Jim íàïèñàâ:
How would I write a query to count the number of instances where a the
PREP field is NULL and when it is not null. I know how to do it
seperately, can I get the numbers in the same query. I want something
like this

Project #PREP_NOTNULL #PREP_NULL

Thanks,

Jim
Dec 18 '06 #3
que
select count(*) nulls, (select count(*) from your_table where not
isnull(prep)) not_nulls from your_table where isnull(prep)
>
Is there a way to get the counts in the same ROW
Dec 20 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
10761
by: Aaron C | last post by:
Hi, I'm trying to do an insert with the following statement: INSERT INTO user VALUES ( 'ag@ag.com','ag','Aaron','Chandler','','','La Mirada','CA',90638,714,'',''); and I'm getting the error message "Column count doesn't match value count at row 1".
10
42396
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed to be optional, it's very definition is it's a necessary link to the parent table and part of the definition. If it's optional it shouldn't be part of the definition of a table and should be in a linking table instead. Comments?
5
3142
by: gelangov | last post by:
I need to update the columns to blanks if it has Nulls; Most of the time we do not have Nulls and to check that in a 3.2 million table it takes 2 minutes and 45 seconds. (To do select count(*) from <table> where <columnname> is Null) . I just cut and pasted a part from my stored procedure: EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE ' +@columnname + ' is Null ) >0 BEGIN
13
8670
by: Federico Balbi | last post by:
Hi, I was wondering if PGSQL has a function similar to binary_checksum() of MS SQL Server 2000. It is pretty handy when it comes to compare rows of data instead of having to write long boolean expressions. binary_checksum() takes a list of fields and it returns an integer value which sumarize the row content. Thanks, Fed
1
2723
by: griemer | last post by:
I have a database like this id, field1,field2,field3,field4,field5 Database contains 100 rows, some rows have no fields filled, some 1field , some 2 fields etc. How would i count the number of fields filled in total? So the outcome is (number of fields filled in row1)+(number of fields
17
4513
by: Mark A | last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP 11). Using the SAMPLE database, tables EMP and EMLOYEE. In the followng stored procedure, 2 NULL columns (COMM) are selected into 2 different SP variables and compared for equal. They are both NULL, but do not compare as equal. When the Not NULL columns (SALARY) are compared, they do compare as equal.
3
1979
by: john | last post by:
SELECT DISTINCT Ap2.test1, Count(Ap2.test1) AS AantalVantest1 FROM Ap2 GROUP BY Ap2.test1; gives me a record count per unique value of test1 field. How do I include the count of all the records without a value? The table resulting from the query just shows an empty value with 0 as the count which is not correct. thanks, john
3
12272
ADezii
by: ADezii | last post by:
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert...
6
7514
by: leegold58 | last post by:
Hi, What is the SQL to count the number of null values in a specific column in a table? I knw this must be simple but I've been Googling this for a long time and have not the SQL to do this. Thanks for the support,
0
7977
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7894
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8044
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8261
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6705
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
3893
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3936
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1496
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.