在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 theDataTable
read from data base, thenLINQ
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屋!