asked on

Sql Server Count if

Hi,

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?

Thanks!

Swapnil Nirmal

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

Wm Allen Smith

ASKER

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.

Thanks.

Swapnil Nirmal

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:

=IF(ISERROR(VLOOKUP(C9,C$5:C8,1,0)),1,0)

Glenn Ray

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:
=SUM(IF(FREQUENCY(D1:D300000,D1:D300000)>0,1))

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

-Glenn

Mlanda T

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
GROUP BY IDNumber

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
GROUP BY IDNumber
HAVING COUNT(*) > 1

Open in new window

sameer2010

Just adding one more line to above comment

SELECT IDNumber, CASE WHEN COUNT(*) = 1 THEN 1 ELSE 0 END AS NumberOfOccurrences
FROM IDNumbers
GROUP BY IDNumber

Open in new window

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial