SUM聚合函数 [英] SUM aggregate function

问题描述

我有以下代码。如何从DocStatus中赚取金额？

Hi, i have the following codes. How can i make a sum from the DocStatus?

SELECT CustomerName,[0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed
FROM
(
    SELECT P.DocStatus,C.CustomerName,P.CustomerID
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID
) p

PIVOT
(
    COUNT (CustomerID) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])

) AS pvt;

i试图将SUM功能放在不同的地方（例如。在PIVOT中，在2 SELECT语句中）但似乎没有效果。

i have tried to put the SUM function at different places(eg. inside PIVOT, at the 2 SELECT statement) but none seems can work.

推荐答案

如果下面的值相互对应：

[0] - 待定，

[1] - Save_As_Draft

[2] - Spec_Confirmed

[3] - Request_Revision

[ 4] - 提取

[5] - Pending_RND

[6] - Pending_QC

[7] - Manday_Confirmed

您的透视查询应如下所示：

If below values correspond eachother:
[0] - Pending,
[1] - Save_As_Draft
[2] - Spec_Confirmed
[3] - Request_Revision
[4] - Withdraw
[5] - Pending_RND
[6] - Pending_QC
[7] - Manday_Confirmed
your pivot query should looks like:

SELECT CutomerName, [0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed
FROM (
    SELECT CustomerName, [1], [2], [3], [4], [5], [6], [7]
    FROM
    (
        SELECT P.DocStatus, C.CustomerName,P.CustomerID
        FROM dbo.CRF_Project P
        INNER JOIN dbo.CustomerList C
        ON P.CustomerID = C.CustomerID
    ) dt
    PIVOT
    (
        COUNT (CustomerID) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])
    ) AS pvt
) AS T

你看到了不同吗？

Do you see the diffetence?

我会建议一个更好的解决方案，但就你想要的我会先给你看我的解决方案

i would suggest a better solution but as far as what you want i will give you first then see my solution

DECLARE @Customer AS TABLE (Customerid INT, CustomerName VARCHAR(15))
DECLARE @Project AS TABLE (Customerid INT, DocStatus INT)

INSERT INTO @Customer
VALUES
(1,'Customer1'),(2,'Customer2'),(3,'Customer3'),(4,'Customer4'),(5,'Customer5'),
(6,'Customer6'),(7,'Customer7'),(8,'Customer8'),(9,'Customer9'),(10,'Customer10'),
(11,'Customer11'),(12,'Customer12'),(13,'Customer13'),(14,'Customer14')

INSERT INTO @Project
VALUES
(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),
(8,1),(9,2),(10,3),(11,4),(12,5),(13,6),(14,7),
(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,1),
(8,2),(9,3),(10,4),(11,5),(12,6),(13,7),(14,1)

SELECT P.DocStatus, C.CustomerName, P.CustomerID
FROM   @Project P
       INNER JOIN @Customer C
            ON  P.CustomerID = C.CustomerID
UNION ALL
SELECT 8 AS Docstatus, c.CustomerName, COUNT(*)
FROM   @Project P
       INNER JOIN @Customer C
            ON  P.CustomerID = C.CustomerID
GROUP BY
       C.Customerid, c.CustomerName




SELECT CustomerName, [0] AS Pending, [1] AS Save_As_Draft, [2] AS Spec_Confirmed, [3] AS
       Request_Revision, [4] AS Withdraw, [5] AS Pending_RND, [6] AS Pending_QC, [7] AS
       Manday_Confirmed,[8] AS Total
FROM   (
        SELECT P.DocStatus, C.CustomerName, P.CustomerID, 1 AS customercount
        FROM   @Project P
               INNER JOIN @Customer C
                    ON  P.CustomerID = C.CustomerID
        UNION ALL
        select 8 AS Docstatus, c.CustomerName,c.Customerid, COUNT (p.Customerid)
        FROM  @Project P
               INNER JOIN @Customer C
                    ON  P.CustomerID = C.CustomerID
        GROUP BY C.Customerid, c.CustomerName
       ) p

       PIVOT(
        SUM(customercount)  FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7],[8])
       ) AS pvt;
/* below is my solution see it is read the source only once  hope this one is better readable than the pivot and have a better performance too*/
SELECT C.CustomerName,
       SUM(CASE WHEN docStatus =0 THEN 1 ELSE 0 END) AS Pending,
       SUM(CASE WHEN docStatus =1 THEN 1 ELSE 0 END) AS Save_As_Draft,
       SUM(CASE WHEN docStatus =2 THEN 1 ELSE 0 END) AS Spec_Confirmed,
       SUM(CASE WHEN docStatus =3 THEN 1 ELSE 0 END) AS Request_Revision,
       SUM(CASE WHEN docStatus =4 THEN 1 ELSE 0 END) AS Withdraw,
       SUM(CASE WHEN docStatus =5 THEN 1 ELSE 0 END) AS Pending_RND,
       SUM(CASE WHEN docStatus =6 THEN 1 ELSE 0 END) AS Pending_QC,
       SUM(CASE WHEN docStatus =7 THEN 1 ELSE 0 END) AS Manday_Confirmed,
       SUM(1) AS Total
FROM   @Project P
       INNER JOIN @Customer C
            ON  P.CustomerID = C.CustomerID
GROUP BY c.CustomerName

这就是你想要的我认为hopt哪个更好看和性能

well this is what you want i think hopt which one is better in looking and performance wise

SELECT CustomerName,[0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
FROM
(
    SELECT P.DocStatus,C.CustomerName,P.CustomerID
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID
) p

PIVOT
(
    COUNT (CustomerID) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])

) AS pvt;

只需添加[0] + [1] + [2] + [3] + [4] + [5] + [6] + [7]为后面的总计

just add the [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total at behind

这篇关于SUM聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！