I am trying to select grouped data from one table and add the number of records from another.

The structure of the tables is as follows:

t1: id created_at (timestamp) price (double) t2: id created_at (timestamp) type (enum(1,2))

I'm trying to make this request:

 select date(t1.created_at) as date, t1.price, (select count(*) from t2 t21 where date(t1.created_at) = date(t21.created_at) and type = 1) as cnt1, (select count(*) from t2 t22 where date(t1.created_at) = date(t22.created_at) and type = 2) as cnt2 from t1 group by date, t1.price

I get the error subquery uses ungrouped column "t1.created_at" from outer query

How can I select count from another table by date?

    1 answer 1

     SELECT date(t1.created_at) as date, t1.price, SUM(CASE WHEN type = 1 THEN 1 END) count1, SUM(CASE WHEN type = 2 THEN 1 END) count2 FROM t1 LEFT /* или INNER */ JOIN t2 ON date(t1.created_at) = date(t2.created_at) group by date(t1.created_at), t1.price

    UPDATE: The previous request is invalid.

     SELECT t11.date, t11.price, COALESCE(SUM(CASE WHEN t2.type = 1 THEN 1 END)) count1, COALESCE(SUM(CASE WHEN t2.type = 2 THEN 1 END)) count2 FROM ( SELECT DISTINCT date(t1.created_at) as date, t1.price as price FROM t1 ) t11 LEFT /* или INNER */ JOIN t2 ON t11.date = date(t2.created_at) group by t11.date, t11.price
    • Added else 0, got rid of null. - ilyaplot
    • Well, this is already to taste ... - Akina
    • Counts wrong, unfortunately - ilyaplot
    • I have only 6 entries in table t2, and the sum of the entire sample of 9 is ilyaplot
    • Type of deviation? because the only problem point visible is the incorrect binding condition date(t1.created_at) = date(t2.created_at) . the sum from the whole sample is 9 Ummm ... and the group by date(t1.created_at), t1.price what, gets several records from the table t1 ??? - Akina