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
source to share
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
source to share
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
source to share
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)
source to share