[Last Call] Learn how to a build a cloud-first strategyRegister Now


Sql Server Count if

Posted on 2014-08-29
Medium Priority
Last Modified: 2014-09-03

I have an EXCEL 2010 spreadsheet that contains a column of ID numbers. There are over 300,000 rows; there are duplicates. I have a formula in a helper cell that count the occurances of an within the  range--if the count is 1, then the helper cell value is 1, if not 1, then the helper cell value is zero( =IF(COUNTIFS(D$1:D16019,D16019)=1,1,0).  Then I can count the helper cells with the value  of 1.

The problem is that this crashes excel once the row count gets over 200K.  Can this be done  with a simple  SQL  query?

Question by:wasmithpfs
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40293423
Use the following sql que

Count ID
From MyTable
Where ID = [Current ID]

Open in new window

If the result is more than 1 then its repeated

Author Comment

ID: 40293450
Swapnil Nirmal,

Thank you for your quick response. Your solution will not work for me. First, there are over 18000 unique ID's in the list of over 300000 rows. In addition, I just need the helper cell value adjacent to the id cell to be either 1 or 0 so that I can filter  the unique ID's.

LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40293485
Count IF on large range for multiple cells will always lead to crash of excel. Instead of countif you can use Vlookup.

I am little bit confused about you intention but check if this help:

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 27

Expert Comment

by:Glenn Ray
ID: 40293489
Are you trying to determine the total number of unique values in column D?  OR, do you actually want a new listing of unique values?

If the latter, then the "Remove Duplicates" function will work for you:  just select that column as your defining criteria.

If the former, this formula will produce the total of unique values:

I confirmed this with a sample of 300000 records; you'll want to change the row range as needed.

LVL 31

Expert Comment

ID: 40293684
Assuming your data is in a SQL table called IDNumbers with a column called IDNumber (this should work with most databases that support SQL - SQL Server, Access, mySQL, etc)

To generate a list of UNIQUE ID Numbers from your data and show the number of times each appears
SELECT IDNumber, COUNT(*) AS NumberOfOccurrences
FROM IDNumbers

Open in new window

If you only want those that appear multiple times - also showing how many times they appear
SELECT IDNumber, COUNT(*) AS NumberOfOccurrences
FROM IDNumbers

Open in new window

LVL 13

Expert Comment

ID: 40296243
Just adding one more line to above comment
SELECT IDNumber, CASE WHEN COUNT(*) = 1 THEN 1 ELSE 0 END AS NumberOfOccurrences
FROM IDNumbers

Open in new window

LVL 85

Accepted Solution

Rory Archibald earned 1500 total points
ID: 40296644
If you can sort the data by column D first, you can use a much more efficient formula:


and copy down, then sum that column.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question