CODEWITHSUNDEEP
mysql
jnthnclrk
jnthnclrk

Reputation: 468

How do I create a date range query when the date is stored in separate rows?

A table called "report":

|id|report_field_id|report_value_row_id|report_value|
|1|1|1|2012-01-05|
|2|2|1|Some text|
|3|3|1|109.00|
|4|1|2|2012-01-06|
|5|2|2|More text|
|6|3|3|889.73|
|7|1|3|2012-01-07|
|8|2|3|More text|
|9|3|3|889.73|

I would like create a select query that sums report_value where report__field_id is equal to 3 and where report_field_id 1 is a certain date or between a certain date range. I'm not sure if I should use a sub query, join or what. I'm still quite new to MySQL, but I really need this to work for a project I'm working on.

Upvotes: 0

Views: 72

Answers (1)

Eric
Eric

Reputation: 95113

Well, let's try this:

select
    sum(r.report_value) as value
from
    report r
where
    r.report_field_id = 3
    and exists
    (
        select
            1
        from
            report r1
        where
            r1.report_value_row_id = r.report_value_row_id
            and str_to_date(report_value, '%Y-%m-%d') between '2012-01-04' and '2012-01-05'
            and r1.report_field_id = 1
    )

This query being said, your table design is rather abysmal. Why don't you just have columns for Date, Name, and Value? That way, you could just do select sum(value) from report where date between '2012-01-04' and '2012-01-05' and make your life easy! I would sincerely question your database design practices. This is a relational database, not a key-value store--use it like one.

Upvotes: 1

Related Questions