SQL Server: sum above field changed after adding LEFT JOIN

I have an example request 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:

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

      

But I wanted to get this result after adding ISNULL

and sumPrice

:

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

      

+3


source to share


3 answers


I found a way to use the inline select:

SELECT 
    ISNULL(v2.pId, v1.pId) AS pId,
    ISNULL(v2.pName, v1.pName) AS pName,
    (SELECT COUNT(*) FROM view1 vi WHERE vi.kind = 1 AND vi.pId = ISNULL(v2.pId, v1.pId)) 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)

      




And this one:

SELECT 
    ISNULL(v2.pId, v1.pId) AS pId,
    ISNULL(v2.pName, v1.pName) AS pName,
    ISNULL(tc.Counts, 0) AS countKind1,
    SUM(v2.price) AS sumPrice
FROM 
    view1 v1
    LEFT OUTER JOIN
    view2 v2 ON v1.id = v2.id
    LEFT OUTER JOIN
    (SELECT vi.pId, COUNT(*) Counts FROM view1 vi WHERE vi.kind = 1 GROUP BY vi.pId) AS tc ON tc.pId = ISNULL(v2.pId, v1.pId)
GROUP BY
    ISNULL(v2.pId, v1.pId),
    ISNULL(v2.pName, v1.pName),
    tc.Counts

      

0


source


Your request looks a little strange. I can create several ways to get the desired result, but I'm not sure what the value is behind your data. In general, I always advise grouping your data as early as possible, so you can probably merge view1

and then join pId

.

Here's a query that gives your results though



;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 
), cte1 as (
SELECT 
    ISNULL(v2.pId, v1.pId) AS pId,
    ISNULL(v2.pName, v1.pName) AS pName,
    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)
), cte2 as (
    select pName, sum(case when kind = 1 then 1 else 0 end) as countKind1
    from view1
    group by pName
)
select
    c1.pId,
    c1.pName,
    isnull(c2.countKind1, 0) as countKind1,
    c1.sumPrice
from cte1 as c1
    left outer join cte2 as c2 on c2.pName = c1.pName

      

SQL

+2


source


Like @Roman's comment, you will want to join the pID. You will also need to change the connection JOIN

to FULL OUTER

since it view1

does not have a "c" Finally, SUM(Price)

it looks wrong - if this is the price for each, you need to use a different aggregate, for example Max

:

;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 WHEN v1.kind = 1 THEN 1 ELSE 0 END) AS countKind1,
    MAX(v2.price) AS maxPrice
FROM 
    view1 v1
    FULL OUTER JOIN
    view2 v2 ON v1.pid = v2.pid
GROUP BY
    ISNULL(v2.pId, v1.pId),
    ISNULL(v2.pName, v1.pName)

      

SqlFiddle here

+1


source







All Articles