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
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
1
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
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
1
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来检查它。