samedi 9 mai 2015

SQL Server : Sum over a field changed after adding a left join

I have a sample query like this:

;WITH view1 AS (
    SELECT 1 AS id, 1 AS pId, 'a' AS pName, 1 AS kind 
    UNION ALL SELECT 2, 1, 'a', 2 
    UNION ALL SELECT 3, 1, 'a', 1 
    UNION ALL SELECT 4, 2, 'b', 2 
    UNION ALL SELECT 5, 2, 'b', 2 
    UNION ALL SELECT 6, 2, 'b', 1 
), view2 AS (
    SELECT 1 AS id, 1 AS pId, 'a' AS pName, 5 AS price
    UNION ALL SELECT 3, 3, 'c', 3 
    UNION ALL SELECT 6, 2, 'b', 3 
)   
SELECT 
    ISNULL(v2.pId, v1.pId) AS pId,
    ISNULL(v2.pName, v1.pName) AS pName,
    SUM(CASE v1.kind WHEN 1 THEN 1 ELSE 0 END) AS countKind1,
    SUM(v2.price) AS sumPrice
FROM 
    view1 v1
    LEFT OUTER JOIN
    view2 v2 ON v1.id = v2.id
GROUP BY
    ISNULL(v2.pId, v1.pId),
    ISNULL(v2.pName, v1.pName)

Its result is:

pId | pName | countKind1 | sumPrice
----+-------+------------+-----------
1   | a     | 1          | 5
2   | b     | 1          | 3 
3   | c     | 1          | 3

But I wanted this result after adding ISNULLs and sumPrice:

pId | pName | countKind1 | sumPrice
----+-------+------------+-----------
1   | a     | 2          | 5
2   | b     | 1          | 3 
3   | c     | 0          | 3

Aucun commentaire:

Enregistrer un commentaire