时间戳

首页 > 解决方案 > SQL不是基于用户求和，而是在每一行重复聚合总和

sql - SQL不是基于用户求和，而是在每一行重复聚合总和

问题描述

我正在尝试根据有条件的用户总结赢得的收入（在 2/1/21 或之后创建的活动。活动在联系人和/或帐户级别报告）。我试图总结用户赢得的收入。

任何想法？

 SELECT s.fullname, s.businessunitidname,
(SELECT SUM(o.tru_totalgrossrevenuefromusrbpoppproduct)
    FROM Opportunities o
    WHERE   (SELECT SUM(o.tru_totalgrossrevenuefromusrbpoppproduct)
                FROM Opportunities o
                INNER JOIN SystemUser s ON o.ownerid = s.systemuserid
                INNER JOIN Accounts a ON a.accountid = o.customerid
                INNER JOIN contacts c ON a.accountid = c.parentcustomerid
                INNER JOIN activities ac ON c.contactid = ac.regardingobjectid
                WHERE o.statuscode = '3'
                AND o.ownerid = s.systemuserid) >0
            AND
                (SELECT SUM(o.tru_totalgrossrevenuefromusrbpoppproduct)
                FROM Opportunities o
                INNER JOIN SystemUser s ON o.ownerid = s.systemuserid
                INNER JOIN Accounts a ON a.accountid = o.customerid
                INNER JOIN contacts c ON a.accountid = c.parentcustomerid
                INNER JOIN activities ac ON c.contactid = ac.regardingobjectid
                WHERE o.createdon >= '2/1/2021'
                AND o.ownerid = s.systemuserid)>0
            AND
                (SELECT SUM(o.tru_totalgrossrevenuefromusrbpoppproduct)
                FROM Opportunities o
                INNER JOIN SystemUser s ON o.ownerid = s.systemuserid
                INNER JOIN Accounts a ON a.accountid = o.customerid
                INNER JOIN contacts c ON a.accountid = c.parentcustomerid
                INNER JOIN activities ac ON c.contactid = ac.regardingobjectid
                WHERE ac.createdon >= '2/1/2021'
                AND o.ownerid = s.systemuserid)>0
            AND
                (SELECT SUM(o.tru_totalgrossrevenuefromusrbpoppproduct)
                FROM Opportunities o
                INNER JOIN SystemUser s ON o.ownerid = s.systemuserid
                INNER JOIN Accounts a ON a.accountid = o.customerid
                INNER JOIN contacts c ON a.accountid = c.parentcustomerid
                INNER JOIN activities ac ON c.contactid = ac.regardingobjectid
                WHERE  ac.actualend <= '3/31/2021'
                AND o.ownerid = s.systemuserid)>0) Revenue,

        
        
FROM Systemuser s
WHERE s.isdisabled = 'false' AND s.businessunitidname != 'USRBP'

我得到的数据：

Steve Smith   200
John Hands    200
Alex Trey     200
Mike Toth     200

我想要的数据：

Steve Smith 130
John Hands  90
Alex Trey   402
Mike Toth   98

标签： sqlsql-servertsql

解决方案

如果子查询中的连接是主子查询的一部分，则尚不清楚WHERE它们是否会出错SELECT SUM。

假设没问题，那么您可以将其简化为HAVING带有条件SUM聚合的。

  • 请注意，您的原始查询无论如何都无法正常工作，因为SystemUser再次重新加入，因此最终AND o.ownerid = s.systemuserid引用了错误的表引用。
SELECT
    s.fullname,
    s.businessunitidname,
    (SELECT SUM(o.tru_totalgrossrevenuefromusrbpoppproduct)
        FROM Opportunities o
        INNER JOIN Accounts a ON a.accountid = o.customerid
        INNER JOIN contacts c ON a.accountid = c.parentcustomerid
        INNER JOIN activities ac ON c.contactid = ac.regardingobjectid
        WHERE o.ownerid = s.systemuserid
        HAVING SUM(CASE WHEN o.statuscode = '3'
            THEN o.tru_totalgrossrevenuefromusrbpoppproduct END) > 0
           AND SUM(CASE WHEN o.createdon >= '2/1/2021'
            THEN o.tru_totalgrossrevenuefromusrbpoppproduct END) > 0
           AND SUM(CASE WHEN ac.createdon >= '2/1/2021'
            THEN o.tru_totalgrossrevenuefromusrbpoppproduct END) > 0
           AND SUM(CASE WHEN ac.actualend <= '3/31/2021'
            THEN o.tru_totalgrossrevenuefromusrbpoppproduct END) > 0
    ) Revenue
FROM Systemuser s
WHERE s.isdisabled = 'false' AND s.businessunitidname != 'USRBP'

推荐阅读