Q: "a query that would count a record as a +1 if "Attack" appeared in either card_type or split_type."
A: It's not clear what resultset you want to return.
EDIT
Based on your comment/clarification, if I'm understanding this correctly, if the rows returned by the SELECT were something like this example:
card_type split_type
--------- -----------
Attack Attack
Attack Foundation
Attack Crescendo
Foundation Foundation
You want a resultset something like this:
Attack 3
Foundation 2
Crescendo 1
You want 3 returned for "Attack", because three rows had the value 'Attack' in either card_type or split_type. That is, you don't want to return a count of 4, the number of times the value appeared.
To get that result, using a COUNT aggregate, I would run this as two separate queries, and combine the results of the two queries using a UNION ALL set operator. The first query would get a count by just card_type, the second query would get a count by split_type. The "trick" would be for the second query to exclude any rows where the split_type matches the card_type.
The two combined queries would be used as an inline view, the outer query would combine the separate counts using a SUM() aggregate function.
I would do the query using a form something like this:
SELECT c.type
, SUM(c.cnt) AS cnt
FROM ( SELECT ufs.card_type AS `type`
, COUNT(1) AS cnt
FROM ...
GROUP BY ufs.card_type
UNION ALL
SELECT ufs.split_type AS `type`
, COUNT(1) AS cnt
FROM ...
AND NOT (ufs.split_type <=> ufs.card_type)
GROUP BY ufs.split_type
) c
GROUP BY c.type
You'd plug in the row source of the original query two times, replacing the ... in the query above.
Previous answer:
Assuming that you have a SELECT that returns the rows you want checked, one "trick" is to use an expression in the SELECT list to perform a conditional test, and return either a zero or one, and then use SUM() aggregate to return a "count" of the records that meet the specification..
SELECT SUM(IF(ufs.card_type LIKE '%Attack%' OR ufs.split_type LIKE '%Attack%',1,0)) AS cnt
FROM jg1_products p
LEFT JOIN jg1_product_types pt ON p.products_type = pt.type_id
LEFT JOIN jg1_products_to_categories ptc ON p.products_id = ptc.products_id
LEFT JOIN jg1_cards_ufs ufs ON ufs.products_id = p.products_id
WHERE type_handler LIKE "%product_cards%"
AND ptc.categories_id = 89
This query returns a single row, unlike your original query that returns multiple rows. (Again, it's not clear what resultset you want returned; if you actually want to return a count for each distinct card_type, which would be returned if we included a GROUP BY ufs.card_type clause.
MySQL also provides a convenient shorthand for the boolean expression: the evaluation of a boolean expression returns 1 if TRUE, 0 if FALSE, and NULL if NULL. So this expression:
SELECT SUM(ufs.card_type LIKE '%Attack%' OR ufs.split_type LIKE '%Attack%')
FROM ...
is equivalent to the expression in the query above, except for the handling of NULL values.
It's not clear whether you want to check if the column "contains" the string 'Attack' as part of the string, or is the entire string; to check if the value of the column is exactly equal to 'Attack', use the equality comparison instead of LIKE
SELECT SUM(ufs.card_type = 'Attack' OR ufs.split_type = 'Attack') AS cnt
FROM ...
NOTE
DISTINCT is not a function, it's a keyword.
The valid syntax is SELECT DISTINCT expr1, expr2, ... exprN FROM ....
It's invalid to include the DISTINCT keyword multiple times after the SELECT keyword, or in a position other than immediately following SELECT. (The DISTINCT keyword can also be included the COUNT() aggregate function, e.g. SELECT COUNT(DISTINCT expr), but that's entirely different than SELECT DISTINCT.
The parens are entirely ignored. That is, SELECT DISTINCT(foo) is identical to SELECT DISTINCT foo. Including parens is entirely unnecessary, and makes it look like DISTINCT is a function (which it is not.)
