0

I have a MariaDB database, in which I have this table:

ID | Item Name | Item replaced |
-----------------------------------------
0  | Item 1     | Replaced
1  | Item 2     | 
2  | Item 3     | Replaced
3  | Item 3     | 
4  | Item 3     | 
5  | Item 1     | Replaced
6  | Item 2     | Replaced
7  | Item 2     | 
8  | Item 3     | Replaced

I want to count the number of same items and display so ITEM 3 in this case would be '4' as there are four of ITEM 3 in my database but I also want to show how many are replaced so in this case it would be '2' as only two of ITEM 3 is marked as replaced.

End result I want to get is something line

ITEM 1        2 - 2 (2 item 1's and both are are replaced)
ITEM 2        3 - 1 (3 Item 2's and only 1 is replaced)
ITEM 3        4 - 2 (4 Item 3's and 2 are replaced)

I have tried a UNION but that doesnt seem to work for me.

Also it would be helpful if I could count replaced as NULL or Empty.

I found this answer on SO but it's not quite what I want, this case the user is listed more than once so this example wouldnt work for me.

0

You'll need a case expression to count the occurrences of 'Replaced':

select 
 item_name, 
 count(*), 
 count(case when item_replaced = 'Replaced' then 1 end)
from your_table
group by item_name
|improve this answer|||||
  • count(case when item_replaced = 'Replaced' then 1 end) Too complex for MySQL, SUM(tem_replaced = 'Replaced') is enough. – Akina Feb 1 at 19:54
  • I prefer portable code, but that's just me. – mustaccio Feb 1 at 20:09
  • Thanks guys for your help this! I will try it and let you know. – user2787636 Feb 2 at 6:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.