CODEWITHSUNDEEP
mysql
Sam
Sam

Reputation: 1104

Mysql query output blank for one date and works for another date

Hi Below are the details.

Mysql Table Table Structure

Name Type Collation Attributes Null Default

1 interval_start Primary datetime No None
2 ad_id Primary mediumint(9) No None
3 zone_id Primary mediumint(9) No None
4 c_count int(11) No 0
5 i_count int(11) No 0
6 conversion_count int(11) No None

Data inside the table

2020-10-08 09:30:00     216     10008   1   1   0
2020-10-08 10:30:00     216     10008   0   12  0
2020-10-08 10:32:17     216     10008   0   0   1
2020-10-08 10:40:39     216     10008   0   0   2
2020-10-08 13:30:00     216     10008   0   1   0
2020-10-09 09:30:00     216     10008   1   5   0
2020-10-09 10:10:38     216     10008   0   0   1
2020-10-09 10:30:00     216     10008   0   72  0
2020-10-09 10:56:02     216     10008   0   0   15

Mysql Query Output gives proper

SELECT
    *
FROM
    (
    SELECT
        SUM(i_count),
        DATE(interval_start) as date_time
    FROM
        rv_data_reports s
) e
where date_time = '2020-10-08';

but for below query it renders blank output.

SELECT
    *
FROM
    (
    SELECT
        SUM(i_count),
        DATE(interval_start) as date_time
    FROM
        rv_data_reports s
) e
where date_time = '2020-10-09';

Upvotes: 0

Views: 35

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

The sub query sums over the whole dataset but without a group by the date_time returned is non determinate and actually your first query returns an incorrect sum for the date.

You don't say what you want the sub query to do but if you want to sum by date you should include a group by for example

SELECT
    *
FROM
    (
    SELECT
        SUM(i_count),
        DATE(interval_start) as date_time
    FROM
        T s
        GROUP BY DATE(interval_start)
) e
where date_time = '2020-10-08';

The sub query now returns the sums for both dates. The query is clumsy you could use a having clause instead of a main query for example

   SELECT
        SUM(i_count),
        DATE(interval_start) as date_time
    FROM
        T s
        GROUP BY DATE(interval_start) having date_time = '2020-10-08'

If you want to sum for a specified data use a where clause

SELECT
        SUM(i_count),
        DATE(interval_start) as date_time
    FROM
        T s
        where DATE(interval_start) = '2020-10-08'

Upvotes: 1

Related Questions