SQL sql查询以查找所有行的总和和重复数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3679148/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql query to find sum of all rows and count of duplicates
提问by ARK
If data is in the following format:
如果数据采用以下格式：
SID TID Tdatetime QID QTotal
----------------------------------------
100 1 01/12/97 9:00AM 66 110
100 1 01/12/97 9:00AM 66 110
100 1 01/12/97 10:00AM 67 110
100 2 01/19/97 9:00AM 66 .
100 2 01/19/97 9:00AM 66 110
100 2 01/19/97 10:00AM 66 110
100 3 01/26/97 9:00AM 68 120
100 3 01/26/97 9:00AM 68 120
110 1 02/03/97 10:00AM 68 110
110 3 02/12/97 9:00AM 64 115
110 3 02/12/97 9:00AM 64 115
120 1 04/05/97 9:00AM 66 105
120 1 04/05/97 10:00AM 66 105
I would like to be able to write a query to sum the QTotal column for all rows and find the count of duplicate rows for the Tdatetime column.
我希望能够编写一个查询来对所有行的 QTotal 列求和，并找到 Tdatetime 列的重复行数。
The output would look like:
输出将如下所示：
Year Total Count
97 | 1340 | 4
采纳答案by Zafer
The following query may help:
以下查询可能会有所帮助：
SELECT
'YEAR ' + CAST(sub.theYear AS VARCHAR(4)),
COUNT(sub.C),
(SELECT SUM(QTotal) FROM MyTable WHERE YEAR(Tdatetime) = sub.theYear) AS total
FROM
(SELECT
YEAR(Tdatetime) AS theYear,
COUNT(Tdatetime) AS C
FROM MyTable
GROUP BY Tdatetime, YEAR(Tdatetime)
HAVING COUNT(Tdatetime) >= 2) AS sub
回答by Abe Miessler
This will work if you really want to group by the tDateTime column:
如果您真的想按 tDateTime 列分组，这将起作用：
SELECT DISTINCT tDateTime, SUM(QTotal), Count(distinct tDateTime)
FROM Table
GROUP BY tDateTime
HAVING Count(distinct tDateTime) > 1
But your results look like you want to group by the Year in the tDateTime column. Is this correct?
但是您的结果看起来像是要在 tDateTime 列中按年份分组。这样对吗？
If so try this:
如果是这样试试这个：
SELECT DISTINCT YEAR (tDateTime), SUM(QTotal), Count(distinct tDateTime)
FROM Table
GROUP BY YEAR (tDateTime)
HAVING Count(distinct tDateTime) > 1
回答by Conrad Frix
SELECT
YEar + year(Tdatetime),
SUM ( QTotal ),
(SELECT COUNT(*) FROM (
SELECT Tdatetime FROM tDateTime GROUP BY Tdatetime
HAVING COUNT(QID) > 1) C
FROM
Tdatetime t
GROUP BY
YEar + year(Tdatetime)
回答by user443790
This is the first time I have asked a question on stackoverflow. It looks like I have lost my original ID info. I had to register to login and add comments to the question I posted.
这是我第一次在 stackoverflow 上提问。看来我丢失了原始 ID 信息。我必须注册才能登录并对我发布的问题添加评论。
To answer OMG Ponies question, this is a SQL Server 2008 database. @Abe Miessler , the row with SID 120 does not contain duplicates. the first row for SID 120 shows 9:00AM in the datetime column , and the second row shows 10:00AM.
要回答 OMG Ponies 问题，这是一个 SQL Server 2008 数据库。@Abe Miessler ，SID 120 的行不包含重复项。SID 120 的第一行在日期时间列中显示 9:00AM，第二行显示 10:00AM。
@Zafer, your query is the accepted answer. I made a few minor tweaks to get it to work. Thanks. Thanks due to Abe Miessler and the others for your help.
@Zafer，您的查询是公认的答案。我做了一些小的调整来让它工作。谢谢。感谢 Abe Miessler 和其他人的帮助。
回答by Tomasz Kowalczyk
You must do SELECT from this table GROUPing by QTotal, using COUNT(subSELECT from this table WHERE QTotal is the same). If I only I had time I would write you SQL statement, but it'll take some minutes.
您必须使用 COUNT(subSELECT from this table WHERE QTotal is the same)从这个表中按 QTotal 进行 SELECT 分组。如果我有时间，我会为您编写 SQL 语句，但这需要几分钟。
回答by Nix
Something like:
就像是：
select Year(Tdatetime) ,sum(QTotal), count(1) from table group by year(Tdatetime )
or full date
或完整日期
select Tdatetime ,sum(QTotal), count(1) from table group by year(Tdatetime)
Or your ugly syntax ( : ) )
或者你丑陋的语法（:)）
select 'Year ' + cast(Year(tdatetime) as varchar(4))
+ '|' + cast(sum(QTotal) as varchar(31))
+ '|' + cast(count(1) as varchar(31))
from table group by year(Tdatetime )
Or do you want just the year? Sum all columns? Or just by year?
或者你只想要年份？对所有列求和？还是只是按年？
