felixRo felixRo - 2 months ago 8
MySQL Question

mysql SELECT columns but use WHERE to only one

I have this query:

SELECT i.d, COUNT(id) AS dr, COUNT(id2) AS dn, SUM(eq) AS eq_sum, COUNT(thx) AS thx_count
FROM dsd
INNER JOIN
(
SELECT COUNT(id) AS d FROM ds
) i


Now I want to use WHERE only to column
thx
like WHERE thx="y" so it will count only all values with "y" but If I just add WHERE at the end of the query it will effect other columns as well which I don't want to. How to do this?

Answer

Then change your COUNT(thx) AS thx_count to below using CASE expression like

COUNT(CASE WHEN thx = 'y' THEN 1 ELSE 0 END) AS thx_count

(OR)

SUM(CASE WHEN thx = 'y' THEN 1 ELSE 0 END) AS thx_count