I have Fruits table with two columns Item and Count
| Item | Count |
|---|---|
| Apple | 5 |
| Banana | 3 |
| Apple | 2 |
| Banana | 7 |
| Kiwi | 3 |
I need output as
| Item | Count |
|---|---|
| Apple | 7 |
| Banana | 10 |
| Kiwi | 3 |
I tried using :
select
[Items] ,
sum(case when [Items] = 'Apple' then 1 else 0 end) as Apple,
sum(case when [Items] = 'Banana' then 1 else 0 end) as Banana,
sum(case when [Items] = 'Kiwi' then 1 else 0 end) as Kiwi
from
dbo.Fruits
group by
[Items]
order by [Items]
But it gives me wrong output
| Items | Apple | Banana | Kiwi |
|---|---|---|---|
| Apple | 2 | 0 | 0 |
| Banana | 0 | 2 | 0 |
| Kiwi | 0 | 0 | 1 |
Could you please suggest thank you.
You don't need conditional aggregation in this use case. Their purpose is to only count or sum values from specific rows (and ignore others). But you want to consider all rows.
Even if you don't exclude rows, your query counts the occurences of items in rows, not the values they have in the Count column. There are two rows with Item Apple, so you get 2.
A trivial GROUP BY clause is enough here to produce the expected result, just sum up the Count values per Item:
SELECT
Item,
SUM([Count]) AS [Count]
FROM fruits
GROUP BY Item
ORDER BY Item;
See this sample fiddle