'Refactor with FILTERS and same condition in WHERE

Any help for refactoring this query? Thank you

SELECT filter(count(*), WHERE action='INIT_PMS' AND error IS TRUE AND manualSelected IS NOT NULL) AS 'Error',
  filter(count(*), WHERE action='INIT_PMS' AND error IS FALSE AND manualSelected IS NOT NULL) AS 'No Error',
  filter(count(*), WHERE action='INIT_PMS' AND error IS NOT NULL AND manualSelected IS NOT NULL) AS 'No Value'
FROM MyDatabase SINCE 4 MONTHS AGO TIMESERIES
mysqlsqlrefactoringnewrelic


Solution 1:[1]

You could just remove the duplicate filters from each filter() clause and add it them as generic filters after the FROM like this:

SELECT filter(count(*), WHERE error IS TRUE) AS 'Error',
  filter(count(*), WHERE error IS FALSE) AS 'No Error',
  filter(count(*), WHERE error IS NOT NULL) AS 'No Value'
FROM MyDatabase where action='INIT_PMS' AND manualSelected IS NOT NULL SINCE 4 MONTHS AGO TIMESERIES

..or you probably could use facet cases() if you don't want many SELECT options:

SELECT count(*) FROM MyDatabase where action='INIT_PMS' AND manualSelected IS NOT NULL facet cases(where error IS TRUE as 'Error', where error IS FALSE as 'No Error', where error IS NOT NULL as 'No Value') SINCE 4 MONTHS AGO TIMESERIES

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 nobrac

Related Questions