mysql query with subqueries returning more than one row

Question

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.

Saharsh Shah · Accepted Answer · 2013-12-12 13:20:19Z

7

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;

edited Dec 12, 2013 at 13:20

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

answered Dec 12, 2013 at 13:18

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcus Adams Over a year ago

Way better than the sub queries the OP asked for.

Collectives™ on Stack Overflow

mysql query with subqueries returning more than one row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related