Counting fields greater than zero in Access

Question

1.00/5 (1 vote)

See more:

How do I get the count of a field but only when the value of the field is greater than zero. For example, here is the code to get the count of the field regardless of its value:

Select Name,Count(Amount) From tableName Where ... Group By Name

However, I want to count only amount with values greater than 0.

Thanks

Posted 5-Jun-12 10:49am

Member 8317792

Updated 5-Jun-12 12:06pm

Tim Corey

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 5-Jun-12 16:53pm

What did you try so far? Any problems? :-)
--SA

Member 8317792 5-Jun-12 16:55pm

The above code get all counts even if the value is 0. I want to get the count of only values greater than 0. PS I have many columns not just the amount that i want to get their count only if their values is greater than 0.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Corey · Answer 1 · 2012-06-05T12:03:00

Solution 1

In SQL, if you want the count of a value when it is greater than zero, you would do it like so:

SQL

SELECT SUM(CASE WHEN Amount > 0 THEN 1 ELSE 0 END) AS AmountCount, Name
FROM tableName
GROUP BY Name

You would do the SUM statement for each column you wanted the count on. What it does is it only puts a one if the value meets your criteria (greater than zero). You could adapt this to meet other criteria for other columns too. For example, you could do a count of any value less than zero for a different column.

If you are using Microsoft Access to query the data, you can use the iif statement (yes, two i's) like so:

SQL

SELECT SUM(iif ([Amount] > 0, 1, 0) AS AmountCount, Name
FROM tableName
GROUP BY Name

Posted 5-Jun-12 12:03pm

Tim Corey

Updated 6-Jun-12 6:34am

v2

Comments

VJ Reddy 5-Jun-12 21:00pm

Good answer. 5!

Member 8317792 6-Jun-12 12:30pm

Does this work with OLEDB? anyway I will try it and let you know. Thanks

Tim Corey 6-Jun-12 12:35pm

Sorry, I forgot to post Access-specific query syntax as well. I've updated my post with the info. I believe that is the correct syntax, although I cannot test it right now.

Member 8317792 6-Jun-12 12:50pm

Unrecognized keyword WHEN. So it will not work with oledb :( Still searching for a solution for my problem...

Tim Corey 6-Jun-12 13:15pm

Did you try my updated code for Access? It doesn't use WHEN.

thatraja · Answer 2 · 2012-06-05T14:41:00

Solution 2

SQL

Select [Name],Count(Amount) From tableName Where Amount>0 Group By [Name]

Posted 5-Jun-12 14:41pm

thatraja

Comments

Member 8317792 6-Jun-12 12:31pm

This will not work with my case since I have several columns as I said...

VJ Reddy · Answer 3 · 2012-06-05T15:27:00

The Solution 1 given by Tim Corey is good.

In case you already have the DataTable read from data base, then LINQ can be used as follows:

C#

DataTable groupedData = new DataTable("GroupedData");
groupedData.Columns.Add("Name",typeof(string),null);
groupedData.Columns.Add("AmountCount",typeof(int),null);

tableName.AsEnumerable().GroupBy (d => d.Field<string>("Name")).Select( gr => {
    DataRow row = groupedData.NewRow();
    row["Name"] = gr.Key;
    row["AmountCount"] = gr.Count (g => g.Field<int>("Amount") > 0);
    return row;
}).CopyToDataTable(groupedData,LoadOption.OverwriteChanges);

Counting fields greater than zero in Access

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us