mysql count occurrence of a string

Question

I need to count how many times a couple categories appear in a column. They're storred as strings like Sports, Medicine, the column name is ct.category_name.

This is the query i'm adapting. I'd like a column for every category type.

select co.order_id, co.catalog_item_id, ct.category_name               
from customer_order as co
join item_category as ic on (ic.item_id = co.customer_id )
join category_translations as ct on (ct.category_id = ic.category_id)
where co.paid = 1 and co.customer_id = 22500 and ct.locale = "en"

When I put this in the select statement it counts everything, I can see why, but I'm not sure which direction to go.

count(CASE 
    WHEN ct.category_name = "sports" THEN ct.category_name 
     ELSE 0 
    end) AS 'sports'

Again, i'd like the count for each string to be its own column. Any help would be much appreciated.

When I try:

select co.order_id, co.catalog_item_id, ct.category_name
, SUM(ct.category_name = "sports") AS `sports`
, SUM(ct.category_name = "medici") AS `medicine`


from customer_order as co

join item_category as ic on (ic.item_id = co.customer_id )
join category_translations as ct on (ct.category_id = ic.category_id)


where co.paid = 1 and co.customer_id = 22500 and ct.locale = "en"

It counts sports twice. Wrong place for the when? Results:

`23115  271708  sports  483 483`

zerkms · Accepted Answer · 2013-02-14 01:53:58Z

1

It counts everything because COUNT increments its value for every not null value, and 0 is not NULL.

Possible solutions:

Replace 0 with NULL OR

Use SUM instead of COUNT:

SUM(CASE 
WHEN ct.category_name = "sports" THEN 1
 ELSE 0 
end) AS 'sports'

or even

SUM(ct.category_name = "sports") AS `sports`

answered Feb 14, 2013 at 1:53

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jahrichie Over a year ago

Coo, but when I adapt for a second string, it is only counting the first one, and repeating it. Should this not be in the select?

zerkms Over a year ago

@jahrichie: I'm not sure I understand you. Show your exact code, guessing is not the best way of solving programming issues

jahrichie Over a year ago

I updated the first post with my attempt and result. Thanks in advance

zerkms Over a year ago

@jahrichie: the query is correct. What if you change the second "medici" to 'zerkms'? Would you see 0?

Collectives™ on Stack Overflow

mysql count occurrence of a string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related