如何使用 SUM 函数从 SQL Server 中获取重复记录的总数

沙林盖加尔

我有几个表,我只想检查是否在这些表中找到了某些条件的重复项。

这是我的 SQL 脚本:

--SELECT SUM(intSubCount) //this is first line that gets total no of summary
--Check Duplicate Subscribers Found :
(SELECT
     s.Id, s.EmailAddress,
     (SELECT Name FROM tbl_User_master 
      WHERE Id = s.user_id) AS CreatedBy,
     s.SubscriptionDateTime, d.intSubCount
 FROM 
     (SELECT EmailAddress, COUNT(*) as intSubCount
      FROM tbl_Subscribers 
      WHERE user_id = '1' AND category_id = '17'
      GROUP BY EmailAddress
      HAVING COUNT(*) > 1) AS d
 INNER JOIN 
     tbl_Subscribers s ON s.EmailAddress = d.EmailAddress)

--Check Duplicate From Users Found :
(SELECT
     f.Id, f.Name, f.EmailAddress,
     (SELECT Name FROM tbl_User_master WHERE Id = f.user_id) AS CreatedBy,
     f.CreatedDate, d.intFromCount
 FROM 
     (SELECT EmailAddress, COUNT(*) AS intFromCount
      FROM tbl_From_master 
      WHERE user_id = '1'
      GROUP BY EmailAddress
      HAVING COUNT(*) > 1) AS d
 INNER JOIN 
     tbl_From_master f ON f.EmailAddress = d.EmailAddress)

--Check Duplicate  Categories Found
(SELECT
    c.Id,c.CategoryName,(SELECT Name FROM tbl_User_master WHERE Id = c.user_id) As CreatedBy, d.intCatCount
FROM (
     SELECT CategoryName, COUNT(*) as intCatCount
     FROM tbl_Categories WHERE user_id='1'
     GROUP BY CategoryName
     HAVING COUNT(*) > 1
) AS d
INNER JOIN tbl_Categories c ON c.CategoryName = d.CategoryName)

--Check Duplicate  Templates Categories Found
(SELECT
    t.Id,t.CategoryName,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy, d.intTCatCount
FROM (
     SELECT CategoryName, COUNT(*) as intTcatCount
     FROM tbl_Template_Categories WHERE user_id='1'
     GROUP BY CategoryName
     HAVING COUNT(*) > 1
) AS d
INNER JOIN tbl_Template_Categories t ON t.CategoryName = d.CategoryName)

--Check Duplicate Templates Found
(SELECT
    t.Id,t.TemplateName,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy,t.Created_date, d.intTCount
FROM (
     SELECT TemplateName, COUNT(*) as intTCount
     FROM tbl_Template_master WHERE user_id='1'
     GROUP BY TemplateName
     HAVING COUNT(*) > 1
) AS d
INNER JOIN tbl_Template_master t ON t.TemplateName = d.TemplateName)

现在我刚刚得到了每个查询的 int 变量计数,现在我只想求和 (intSubCount,intFromCount,intCatCount,intTCatCount,intTCount) 看到我这个脚本的第一行

为了运行这个脚本,我得到了这个结果:

(4 row(s) affected)

(2 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(2 row(s) affected)

我怎样才能做到这一点?请大家帮帮我..

------------------------------编辑------------------- -------

我尝试了这个溶胶,但给出了错误的行数

SELECT SUM(intSubCount) as GrandTotal
FROM(
    --Check Duplicate Subscribers Found :
    SELECT COUNT(*) as intSubCount
    FROM tbl_Subscribers WHERE user_id='1' AND category_id='17'
    GROUP BY EmailAddress
    HAVING COUNT(*) > 1

    UNION ALL

    --Check Duplicate From Users Found :
    SELECT COUNT(*) as  intFromCount
    FROM tbl_From_master WHERE user_id='1'
    GROUP BY EmailAddress
    HAVING COUNT(*) > 1

    UNION ALL

    --Check Duplicate  Categories Found
    SELECT COUNT(*) as  intCatCount
    FROM tbl_Categories WHERE user_id='1'
    GROUP BY CategoryName
    HAVING COUNT(*) > 1

    UNION ALL

    --Check Duplicate Templates Categories Found
    SELECT COUNT(*) as  intTCatCount
    FROM tbl_Template_Categories WHERE user_id='1'
    GROUP BY CategoryName
    HAVING COUNT(*) > 1

    UNION ALL

    --Check  Templates Categories Found
    SELECT COUNT(*) as  intTCount
    FROM tbl_Template_master WHERE user_id='1'
    GROUP BY TemplateName
    HAVING COUNT(*) > 1

    --UNION ALL
    ) AS Totals

结果是:

Grand Total :
7
艾姆戴夫

如果您只关心sum每个单独查询的 ,并且每个单独查询只会返回一个值,您可以简单地将它们添加为子选择:

select (select Query 1 total) + (select Query 2 total) + (select Query 3 total) as GrandTotal

另一种选择是union结果和sum结果数据集:

select sum(total) as GrandTotal
from(select total
     from Query 1

     union all    -- the 'all' ensures no duplicate numbers are removed for an accurate total

     select total
     from Query 2

     union all

     select total
     from Query 3
    ) as totals

为清楚起见进行编辑

任何一个:

SELECT 
    --Check Duplicate Subscribers Found :
    (SELECT COUNT(*) as intSubCount
     FROM tbl_Subscribers WHERE user_id='1' AND category_id='17'
     GROUP BY EmailAddress
     HAVING COUNT(*) > 1
    )
    +
    --Check Duplicate From Users Found :
    (SELECT intFromCount
     FROM tbl_From_master WHERE user_id='1'
     GROUP BY EmailAddress
     HAVING COUNT(*) > 1
    )
    +
    --Check Duplicate  Categories Found
    (SELECT intCatCount
     FROM tbl_Categories WHERE user_id='1'
     GROUP BY CategoryName
     HAVING COUNT(*) > 1
    )
as GrandTotal

或者

SELECT SUM(intSubCount) as GrandTotal
FROM(
    --Check Duplicate Subscribers Found :
    SELECT COUNT(*) as intSubCount
    FROM tbl_Subscribers WHERE user_id='1' AND category_id='17'
    GROUP BY EmailAddress
    HAVING COUNT(*) > 1

    UNION ALL

    --Check Duplicate From Users Found :
    SELECT intFromCount
    FROM tbl_From_master WHERE user_id='1'
    GROUP BY EmailAddress
    HAVING COUNT(*) > 1
    
    UNION ALL

    --Check Duplicate  Categories Found
    SELECT intCatCount
    FROM tbl_Categories WHERE user_id='1'
    GROUP BY CategoryName
    HAVING COUNT(*) > 1
    ) AS Totals

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

如何从SQL Server删除重复记录?

来自分类Dev

如何使用SQL Server SUM函数避免指数时间成本?

来自分类Dev

合并SQL Server中的重复记录

来自分类Dev

如何避免在表类型sql server中插入重复记录

来自分类Dev

如何处理SQL Server数据库中的重复记录

来自分类Dev

如何使 MongoDB 的列成为 SQL Server 中的主键并避免重复记录?

来自分类Dev

如何通过 SQL Server 将重复记录的索引排序到另一列中

来自分类Dev

如何删除SQL中的重复记录?

来自分类Dev

SQL Server:如何允许在小表上重复记录

来自分类Dev

SQL Server:如何允许在小表上重复记录

来自分类Dev

oracle sql中如何使用SUM()函数返回的值

来自分类Dev

SQL Server:忽略SUM函数中的字符串

来自分类Dev

SQL Server:忽略SUM函数中的字符串

来自分类Dev

如何使用SQL MAX(SUM())函数

来自分类Dev

使用 Sum 和抽象函数从多表 SQL 查询中获取记录

来自分类Dev

在SQL Server中处理空值和重复记录

来自分类Dev

找出Sql-server中的重复记录

来自分类Dev

从 SQL Server 中的表中选择除了重复记录

来自分类Dev

从 SQL Server 中的视图中删除重复记录

来自分类Dev

从 SQL 查询中获取重复记录

来自分类Dev

如何使用value()函数在SQL Server中迭代XML字段

来自分类Dev

SQL Server:搜索重复记录

来自分类Dev

如何处理SQL中的重复记录?

来自分类Dev

如何处理SQL中的重复记录?

来自分类Dev

使用SQL删除表中的重复记录

来自分类Dev

使用“ AS”语句在SQL Server中创建函数

来自分类Dev

SQL Server中的复合SUM

来自分类Dev

在SQL Server中将CASE语句与SUM函数一起使用

来自分类Dev

如何在SQL Server中选择没有主键的重复记录

Related 相关文章

热门标签

归档