如何COUNT（*）当COALESCE - SQL Server

I have this T-SQL statement that adds up all of col1 and col2 but also needs to return the number of rows returned by the WHERE

我有这个T-SQL语句,它将所有col1和col2相加,但也需要返回WHERE返回的行数

The only way I figured out how to do it is count(FlightLogLegID - (FlightLogLegID - 1)) which results in summing 1 per row. I was hoping there would be a better way that doesn't require the needless addition and subtraction.

我想出如何做的唯一方法是计数(FlightLogLegID - (FlightLogLegID - 1)),这导致每行求和1。我希望有一种更好的方法,不需要不必要的加法和减法。

SELECT 
    coalesce((Select sum(Col1)),0) AS Number1,
    coalesce((Select sum(Col2)),0) AS Number2,
    coalesce((Select count(TableRowID - (TableRowID - 1)), 0) AS Number3
FROM 
    SomeTable 
WHERE ...

Example rows

TableRowID   Col1   Col2
1            5      10
2            3      5
3            12     8

Desired results

Number1   Number2   Number3
20        23        3

2 个解决方案

#1

If it's as simple as your example suggests, with just one row being returned, all you need is this:

如果它就像您的示例所示简单,只返回一行,您只需要:

Select SUM(Col1)
     , SUM(Col2)
     , Count(*)
From   SomeTable
Where  <whatever>

Coalesce wouldn't be an issue: Count(*) will count all records, even ones with nulls.

合并不会是一个问题:Count(*)将计算所有记录,甚至是具有空值的记录。

#2

Is this what you were asking about?

这是你问的问题吗?

SELECT SUM(Col1) [Number1], SUM(Col2) [Number2], COUNT(*) [Number3] 
FROM SomeTable

You can use this SQL Fiddle to check it out.

您可以使用此SQL Fiddle来检查它。

#1