CODEWITHSUNDEEP
mysql
mario
mario

Reputation: 721

select from table with parameters from another table

first table meter_values

|id   |  somedata | date(YY-MM-DD)| status | 
|-----+-----------+---------------+--------|
|1    |  tets     | 20180628      |   6    |
|2    |  setd     | 20180627      |   6    |
|3    |  ewrw5    | 20180701      |   6    |
|4    |  6werww   | 20180730      |   6    |
|5    |  werqwe   | 20180803      |   6    |
|6    |  wrwerw   | 20171130      |   6    |

second table period

| year   |  begin   | end      |
|--------+----------+----------|
| 201807 | 20180626 | 20180704 |    
| 201808 | 20180730 | 20180803 |
| 201801 | 20171228 | 20180104 |
| 201712 | 20171129 | 20171205 |

i need get count(meter_values) where meter_values.status=6 and group by year and date from table period.begin and period.end
example:

| year   |  begin   | end      | count(meter_values) |
|--------+----------+----------+---------------------|
| 201807 | 20180626 | 20180704 |          3          |
| 201808 | 20180730 | 20180803 |          2          |
| 201801 | 20171228 | 20180104 |          0          |
| 201712 | 20171129 | 20171205 |          1          |

i try this query

select *
        from period, 
        (select count(meter_values.id) 
            from meter_values, period
                where meter_values.date>=period.begin 
                    and meter_values.date<=period.end 
                    and meter_values.status=6
                    and period.begin is not null
                    and period.end is not null) 
            as mv
        where period.begin is not null and period.end is not null;

but i get count of all records

| year   |  begin   | end      | count(meter_values) |
|--------+----------+----------+---------------------|
| 201807 | 20180626 | 20180704 |          6          |
| 201808 | 20180730 | 20180803 |          6          |
| 201801 | 20171228 | 20180104 |          6          |
| 201712 | 20171129 | 20171205 |          6          |

Upvotes: 1

Views: 766

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

you could use a inner join and a count 

select a.year, a.begin, a.end, count(*)
from meter_values b 
inner join period a on b.date between a.begin and a.end  and b.status = 6
group by a.year, a.begin, a.end

Upvotes: 3

Related Questions