I'm building a summary in a table that I self-join in order to count the rows corresponding on certain values (in TikiWiki CMS, tracker application). But I have a case where there are two columns that can have the same values, and I need SQL to take the first non-NULL value in a specific order.
Here is an example of the table tiki_tracker_item_fields
where I work:
itemId | fieldId | value
==========================
41 | 236 | Paris
41 | 213 | Paris
14 | 236 |
14 | 213 | Paris
25 | 236 | Paris
25 | 213 |
In the query I want to count all lines that have "Paris" as value, either in field Id 236 or field ID 213. The result here should be: 3.
There is my request:
SELECT COUNT(*) AS sp_actions
FROM
`tiki_tracker_item_fields` AS actions
WHERE
COALESCE(
actions.fieldId = 236,
actions.fieldId = 213
)
AND actions.value = 'Paris'
So this works when I have 'Paris' for field 236, but not when I have Paris for field 213.
And I'm not even sure I could use COALESCE this way, all examples I found put COALESCE in the SELECT statement.
Then there are the performance issues...
You can use count (distinct)
with a case
:
select
count(distinct case when value = 'Paris' then itemId end) cnt
from `tiki_tracker_item_fields`
where fieldId in (236,213);