Problem in SQL query

Question

Problem in SQL query

Navigation

#1 by (3 votes)

0

Good morning

I am making an inquiry to know the number of rows that are currently planted and the rows that were already planted but the harvest ended and therefore the remaining plants are removed for a new planting per farm.

When there is a row planted in the database it should be in status = 1 and type = 'SW' but the status = 2

When a furrow has already been planted and harvested it should be in status = '1' and type = 'ER' .

The process is as follows:

In farm 1, block 1 and groove 1, a sowing is made in the database and it remains like this;

------------------------------------------------------------------------
 id |  id_land | id_block  | id_groove | type | date_register | status |
------------------------------------------------------------------------
 1  |    1     |    1      |    1      |  SW  |  2015-06-12   |    1   |
------------------------------------------------------------------------

When the time has passed and it was harvested, we proceed to eliminate the plants and leave the groove empty, when registering the database it looks like this:

------------------------------------------------------------------------
 id |  id_land | id_block  | id_groove | type | date_register | status |
------------------------------------------------------------------------
 1  |    1     |    1      |    1      |  SW  |  2015-06-12   |    2   |
------------------------------------------------------------------------
 2  |    1     |    1      |    1      |  ER  |  2015-06-12   |    1   |
------------------------------------------------------------------------

The state of the first sowing becomes 2 and the one of no sowing will be state 1, now if it is sown again, it is registered in the database as follows:

------------------------------------------------------------------------
 id |  id_land | id_block  | id_groove | type | date_register | status |
------------------------------------------------------------------------
 1  |    1     |    1      |    1      |  SW  |  2015-06-12   |    2   |
------------------------------------------------------------------------
 2  |    1     |    1      |    1      |  ER  |  2015-06-12   |    1   |
------------------------------------------------------------------------
 3  |    1     |    1      |    1      |  SW  |  2015-06-12   |    1   |
------------------------------------------------------------------------

The state of type 'ER' remains with the same status = 1 and the new planting remains with status = 1 , and finally if the harvest reappears it is recorded again like this:

------------------------------------------------------------------------
 id |  id_land | id_block  | id_groove | type | date_register | status |
------------------------------------------------------------------------
 1  |    1     |    1      |    1      |  SW  |  2015-06-12   |    2   |
------------------------------------------------------------------------
 2  |    1     |    1      |    1      |  ER  |  2015-06-12   |    1   |
------------------------------------------------------------------------
 3  |    1     |    1      |    1      |  SW  |  2016-06-12   |    2   |
------------------------------------------------------------------------
 4  |    1     |    1      |    1      |  ER  |  2016-06-12   |    1   |
------------------------------------------------------------------------

And so the process successively.

I have the following query to know the number of rows that have a block with status = 100 and type = 'ER'

SELECT id_land, COUNT(id_groove) AS units FROM sowing
WHERE type= "ER"
AND status = 100
GROUP BY id_land;

In this query it brings all the records type 'ER' total, but I only want the number of rows that are in that state currently. By date it is not possible to have the same record, then I thought that a better way would be for the MAX (id) and with that I can get the number of rows, but I do not know how to do it.

In this SQLFiddle I have a table with more data

I thank you in advance for any help.

mysql sql

asked by Fabian Sierra 18.11.2016 в 18:35

source

1 answer

Stack's at the UIViewController of iOs Error converting json to fix [closed]

score 3 · Accepted Answer

We are going through steps:

What you need to know, first, is the current state of each row, since the date in your example is always the same, it does not seem that we can be based on the date, so I will make the assumption that the ID is increasing in time and we will use that information to determine which is the last record entered for each one, like this:

select id_land, id_block, id_groove, max(id)
  from sowing
 group by id_land, id_block, id_groove

Now that we know which is the last record (let's say the current record) of each one, we can determine its status by doing a join back to the original table:

select s.*
  from (select id_land, id_block, id_groove, max(id) MaxID
          from sowing
         group by id_land, id_block, id_groove
       ) ur
       inner join sowing s on s.id_land = ur.id_land 
                          and s.id_block = ur.id_block
                          and s.id_groove = ur.id_groove
                          and s.id = ur.MaxID

Now that we know the current state of each furrow, getting the number of those that are planted is trivial:

select count(1)
  from (select s.*
          from (select id_land, id_block, id_groove, max(id) MaxID
                  from sowing
                 group by id_land, id_block, id_groove
               ) ur
               inner join sowing s on s.id_land = ur.id_land 
                                  and s.id_block = ur.id_block
                                  and s.id_groove = ur.id_groove
                                  and s.id = ur.MaxID
       ) EstadoActual
 where type = 'SW'

I do not include the status in my query , because it seems that whenever the seeding ends a new record is inserted, with which the status is redundant and irrelevant, at least for this case.