[SOLVED] Group data with a single statement on CrateDB nested SELECT

Group data with a single statement on CrateDB nested SELECT

How can I group the following query to the time frame in CrateDB?

SELECT * FROM (
SELECT 
    (
    SELECT
      date_bin('1 day'::INTERVAL, time_index, 0) AS time_frame,
      count(*) FROM schema.status
    WHERE processstatus IN ('State_01')
    GROUP BY time_frame
    ORDER BY time_frame DESC
  ) AS parts_good,
    (
    SELECT
      date_bin('1 day'::INTERVAL, time_index, 0) AS time_frame,
      count(*) FROM schema.status
    WHERE processstatus IN ('State_02')
    GROUP BY time_frame
    ORDER BY time_frame DESC
  ) AS parts_bad
)

At the moment I'm getting the following error:

Error! UnsupportedFeatureException[Subqueries with more than 1 column are not supported.]

Maybe with a JOIN I can make it work, but I would like, if possible, to avoid the declaration of date_bin(), GROUP BY and ORDER BY in each SELECT statement, any suggestions?

Thanks!

Solution

I am not entirely sure, what you are trying to achieve, however the following query would give back the good and bad parts for every time_frame

SELECT
  date_bin('1 day'::INTERVAL, time_index, 0) AS time_frame,
  count(*) FILTER ( WHERE processstatus = 'State_01') AS "parts_good",
  count(*) FILTER ( WHERE processstatus = 'State_02') AS "parts_bad"
FROM schema.status
GROUP BY time_frame
ORDER BY time_frame DESC