CODEWITHSUNDEEP
mysqlsql
natsuki_2002
natsuki_2002

Reputation: 25349

mysql query with subqueries returning more than one row

I've got two columns of dates, t_date and s_date. I'm counting the number of records occurring where s_date is either the same date as t_date or within an n day window after t_date occurs.

Here is the query. It returns multiple rows.

select count(id)
from customers
where s_date >= t_date
and s_date <= t_date + 1
group by t_date;

Is there any way to design a query that contains several sub queries that each return multiple rows? So that I can increment the time window? Something like:

select (
    select count(id)
    from customers
    where s_date >= t_date
    and s_date <= t_date + 1
    group by t_date
    ) as c1,
    (
    select count(id)
    from customers
    where s_date >= t_date
    and s_date <= t_date + 2
    group by t_date
    ) as c2;

This query, however, returns a "Sub query returns more than 1 row" error.

Upvotes: 1

Views: 63

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You can use the CASE expression:

SELECT
  SUM(CASE WHEN s_date >= t_date AND s_date <= t_date + 1 THEN 1 ELSE 0 END) AS c1, 
  SUM(CASE WHEN s_date >= t_date AND s_date <= t_date + 2 THEN 1 ELSE 0 END) AS c2
  ...
FROM customers
GROUP BY t_date;

Upvotes: 7

Related Questions