在Access中计数大于零的字段 [英] Counting fields greater than zero in Access

问题描述

仅在字段值大于零时，如何获取字段计数.例如，下面是获取字段计数而不管其值如何的代码:

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

但是，我只想计算大于0的金额.

谢谢

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

Thanks

推荐答案

在SQL中，如果想要一个大于零的值的计数，您可以这样做:

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

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

您将对要依赖的每一列执行SUM语句.它的作用是，如果值满足您的条件(大于零)，则仅放置一个.您也可以对此进行调整以满足其他列的其他条件.例如，对于不同的列，您可以对小于零的任何值进行计数.

如果使用Microsoft Access查询数据，则可以使用iif语句(是，两个i)，如下所示:

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:

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

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

蒂姆·科里给出的解决方案1很好.

如果您已经从数据库中读取了DataTable ，则可以按以下方式使用LINQ :

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:

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);

这篇关于在Access中计数大于零的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！